Issue with automating PDF and email using Windows Task Scheduler

[#1]

Hi all:

I have got a question about automating PDF and email using Windows Task Scheduler:

I have done most of the part which include:

- Set the macro VBScript code in the qvw file. (Tried by pressing the "Test" button and every works). The macro will be triggered at the end of the reload.

- Created a batch file which will open and run the qvw file and leave open, the batch file will stay open for a specific time (remember this because this is the problem) and force the qlikview program to close and then exit.

- Use Windows Task Scheduler to run the batch file every day.

Here are the questions:

- Everything works when I set up a one-off (non-recurring) task (while I am logging in) with the waiting time in the batch file to be a large number. (The reload took about 3 to 4 minutes, but in order to make everything works, the waiting time in the batch file has to be more than 8 minutes. And this waiting time will keep increasing once I put more codes in the macro. When it works, I actually received the email 3 to 4 minutes after the task started (end of reload) but not at the end of the task)

- The email (sometime even the PDF) failed when I scheduled weekly from Monday to Friday at 8:30 AM (I was not at work at that time). When I scheduled two triggers, one at 8:15 and one at 8:30, which will recur weekly and triggered from Monday to Friday, the first one will always failed and the second one will work.

I am sorry if I explained this issue badly but Does anyone has this strange issue? Maybe you guys can share your experience if you use the same method to schedule your emails.

Thank you very much for your time.

PC

[iliyansomlev]

Hi PC,

you run your app with a bat that reloads the app but doesn't close it (/l option)

in the macro you can write Document.Save and then close it or you can close it without saving - my macro ends with

Sub MacroName

......

ActiveDocument.GetApplication.Quit

End Sub

and I don't save it. In the other thread where you have referenced this one I have explained how to set this in the Windows Task Scheduler. You have to tick "Run with highest privileges" box when you schedule the task and Run whether user is logged on or not. That"s it.

Hope this helps you.

[#3]

Hi Iliyan:

Thank you so so much for your reply!!!!

I just saw your post but I haven't try to close it within the macro and now I have a further question.

Do you need to put the qlikview to sleep after the PDF generation function and the email sending function?

If so, how do I find out how long should I take?

Last but not least, do you use Document.Calculate to generate numbers from the dashboard so that you have a dynamic title or text body for your email?

Will this affect the waiting time?

Thank you so much for your time.

Have a great day.

PC

[#4]

Thank you Iliyan!

Moving the close function from batch file to macro solve the long waiting time issue!!

But I still have one last problem:

Everyday, the first scheduled task seems to fail all the time.

If I set only one trigger, there will be no email.

If I set two triggers, the first email will not be received, and the send email will be able to go through.

I try to insert some logging in the macro, and it looks like the first trigger stopped during the End With section in the Email Function.

I also notice that the first task run a little bit slower then the second one.

Do you have any ideas about what is wrong with the first task?

Thank you very much for your time.

PC

[iliyansomlev]

Hi PC,

I am not sure if I understand your problem correctly. You have two separate bat files that run individual (only one per bat file) reloads, or you have two reloads that you run from one bat file?

In our case we use 2 reloads that are run from one bat file (so we have only one task in Windows scheduler). They should not get mixed but should run one after the other - we don't calculate time for the separate reloads but write in the bat file

echo off | clip

between the lines that call these reloads.

I remember that in one of the files we had object that required calculation time and the export produced empty cells only for the first sheet but the other were OK. Could this be your problem? We resolved this by puting

qvDoc.GetApplication.WaitForIdle

- the wait for idle command once the QV sheet is activated, so that there is time for calculation to finish before it is exported to excel.

Hope this helps.

I.

[vikasmahajan]

Run application using batch file , you can schedule jobs in windows server please find attached settings

Vikas

[#7]

Hi All:

I finally make it worked!!

But I have made two changes before it is working, so I am not sure which one is the main reason:

First, I change the order of calling Bullzip printer function and Qlikview print report function.

So before, it was:

- ActiveDocument.PrintReport("Report ID"), "Bullzip PDF Printer", false

- PrintPDFWithBullZip("Some parameter")

And Now It is:

- PrintPDFWithBullZip("Some parameter")

- ActiveDocument.PrintReport("Report ID"), "Bullzip PDF Printer", false

Both order works when pressing "Test" button. But I cannot make it work using Task Scheduler before.

Secondly, I change Bullzip PDF printer as the server's default printer.

Not sure if this would affect the Task Scheduler.

Before I made those changes, the problem I had was the PDF will not generate when there is only one trigger in the Windows Task Scheduler. If I set two trigger for the same task, the second one will work.

I tried to monitoring the task manager when the during the execution. Then I found that the Bullzip PDF program with the name "gui*32" started and then keep running in the background. It will never close which is not the case when it is working. It should generate the PDF file and then close by itself.

This is the first day I make this work so I hope this can keep working forever. Fingers cross!

Thank you Iliyan Somlev and Vikas Mahajan

Best regards,

Paco

[vikasmahajan]

Request you to mark post as correct Answer so that other developer can use the same.

Vikas

[dinuwanbr]

Hi,

Instead of a windows batch file there is a third party scheduler (light weight software) called JIT scheduler. Please try on that and its totally free. You can open your qlikview document using that and at the end of the running macro just add commands to close the document itself.(following lines)

ActiveDocument.Save 

ActiveDocument.GetApplication.Quit

BR,

-Dinu1

[#10]

Hi Vikas:

Thank you very much for your answer but I've never try your post.

Maybe someone can try this and give me feedback.

Thanks