Macro Question - Module Opening Post Reload

[#1]

Hi,

We have recently moved from Qlikview 8.5 to 11, however, we have an issue with macro's opening post reload that we haven't been able to solve yet.

The macro's run to export a number of tables to Excel, and they're set by identifiying the object id and the file path for the Excel location.  The macro's have run without issue for a number of years on QV 8.5, and we haven't amended them in any way since moving to QV11.  However, now, once the report has reloaded the first call command of the sub routine runs and exports the table to Excel as we would expect, but then the module opens up, but with no error messages to indicate an issue.

Does any one have know what could be the cause?

[#2]

Hi,

could you please paste the macro code so that we can see what the possible issue might be.

another thing is qv11 does not support the OnPostReload macro to export when you are scheduling through QEMC.

also check what module security is defined for the macro.

May be this helps.

Thanks & Best Regards,

Kuldeep Tak

[#3]

If you're scheduling it through QEMC it won't run. You need to schedule it via Windows Task Manager (take it off the QEMC scheduler) and run a bat file that does this:

cd C:\Program Files (x86)\QlikView

qv.exe /l C:\QVW Documents\Demo.qvw

However this will KEEP an instance of qv.exe open on the server. So within the Macro, right as it finishes, you need to put the following:

ActiveDocument.GetApplication.Quit

This will close the qv.exe when OnPostReload has finished running and your tables have been exported.

Also go to "Settings -> User Preferences -> Security" and tick everything except "Confirm Launch from Macro"

Press Ctrl-M to get to your Macro code, and make sure "System Access" and "Allow System Access" drop down boxes are selected at the left hand side.

That's *EVERYTHING* you need to get this up and running again

[#4]

Hi

Further to biggin above, the problem continues as I have performed all the same and still encounter the macro opening up on end sub.

I created a very simple macro to  read

Sub RunMe()

Call exportToExcel("ABC","\\Tscnas02\MIS Data Warehouse\Automated report\aTEST.xls")

end sub

sub exportToExcel(objectId, filePath)
dim o
Set o = ActiveDocument.GetSheetObject(objectId)
o.ExportBiff filePath
Set o = nothing

f_name = filePath     
Set oXL=CreateObject("Excel.Application")     
Set oWB=oXL.Workbooks.Open(f_name)   
Set oSH=oWB.Worksheets.Item(1)   

oWB.Save    
oWB.Close          
Set oSH=nothing    
Set oWB=nothing    
Set oXL=nothing

end sub

Now this is simple stuff and I would expect that to be the end.  However, the module opens in a script editor box after exporting the file. 

Really frustrating - is there a server setting issue I have missed here?

[#5]

*quick answer* ActiveDocument.GetApplication.Quit at the end of your Sub (right before End Sub)

If you read my post above you'll see what to do to close the module after OnPostReload has fired.

I recently went through the exact same exercise (except I exported images of the charts) and following what I've outlined above, you'll be able to schedule OnPostReload macro's to fire through WINDOWS Task Scheduler without any issues.

[#6]

Thank you - What you suggest does help close the application at the end, but not the problem that both Biggin and myself have expressed.  The Qlikview Macro scripting window opens each time and on further examination, creates an error where it cannot create an Actix X object bla blah!!  This I found frustrating as it works fine in teh 8.5 ver we still have and want to migrate away from

Good news though is that one I removed the old code that references shortcuts it works fine:

REMOVED from code above

f_name = filePath     
Set oXL=CreateObject("Excel.Application")     
Set oWB=oXL.Workbooks.Open(f_name)   
Set oSH=oWB.Worksheets.Item(1)  

oWB.Save    
oWB.Close          
Set oSH=nothing    
Set oWB=nothing    
Set oXL=nothing

Thanks for all your help though.

[rwunderlich]

Hi Peter,

The whole problem becomes much more manageable if you write the vbs code outside the qvw. That is, run it in a vbs file using cscript. The only thing you have to add is the:

CreateObject("QlikTech.QlikView")

OpenDoc or OpenDocEx

-Rob

[#8]

Certainly worth the try.

Many thanks to all!

[#9]

I actually tried what Rob suggested but it didn't work for me.

The reason why your Macro window opens (and is kept open) is because of the ActiveXObject error. That is entirely different to the OP's issue IMO. You might need to check if Excel is still installed on your server. However, I've had issue with ActiveXObject and it would appear QV10+ doesn't support much except the basic Windows-based ones