Macro to generate xlsx works fine manually but not from the batch through QlikviewManagement Console

[spandita]

Hi,

I am trying to export few charts to Excel (.Xlsx format) through Qlikview Macro and to save it on postrelaod at a particular location. The file works perfectly fine when it is run manually or from the batch (.bat) on double click. But when scheduled to run from the Qlikview Management Console through the external File(.bat file)its generating the Excel Extract but the file is blank. The error is: 'Error: Paste method of Worksheet class failed'. I have checked the permission/location of the file and its not an issue. Please help, its really urgent.

Thanks,

Spandita

[dplr-rn]

Does the server have office installed locally?
i think it may need it if you are using worksheet object

[spandita]

Yes, Office is installed. Other qvw files works fine from the server and generate excel extract

[Brett_Bleess]

Had to find an article to be sure, but I did find one, I just had to update things as the version info was not updated, but this is working as expected given macros will not run via Publisher/Reload Engine when using OnPostReload or OnPostReduceData trigger, those are ignored in this case.  This is mainly due to conflict between the 'task' trying to shut things down post reload, and the trigger trying to run things for a little further explanation.

Here is the article link:  Article

Regards,
Brett

[jerrysvensson]

I guess you are using the same macro code in this app as the ones that is working.

The sheet where the exported diagram reside has to be active. You can do this on the macro.
ActivateSheet()
Add a pause to wait for refresh of diagram. Look in API, it is sleep or wait.

[spandita]

We have tried both options. The document got stuck and did not run at all after the pause.

We also tried adding  ActivateSheet() as a post reload trigger before running the post reload macro..Manual refresh is working fine as well as from windows batch file.But not running when the batch file is triggered from qmc.

I am using following script in batch( .bat) file

"C:\Program Files\QlikView\Qv.exe" /l "\\Filepath\filename.qvw"

Exit

[jerrysvensson]

Hi

You can't use a trigger to Activesheet. It has to be in the macro. All triggers runs in its own scope.

Running it from QMC requires a license for the qlikview publisher service account also. 

Put this in your macro sub:

ActiveDocument.ActivateSheet you need to add which sheet to activate. Look in the API 
ActiveDocument.GetApplication.Sleep 1000  One second sleep

Then run your excel export code