topic Re: Macro Question - Module Opening Post Reload in QlikView

Macro Question - Module Opening Post Reload

Thu, 02 Aug 2012 11:25:50 GMT

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?

Re: Macro Question - Module Opening Post Reload

Fri, 03 Aug 2012 07:41:50 GMT

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

Re: Macro Question - Module Opening Post Reload

Fri, 03 Aug 2012 08:15:44 GMT

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

Re: Macro Question - Module Opening Post Reload

Mon, 06 Aug 2012 12:59:36 GMT

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?

Re: Macro Question - Module Opening Post Reload

Mon, 06 Aug 2012 13:27:08 GMT

*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.

Re: Macro Question - Module Opening Post Reload

Mon, 06 Aug 2012 15:19:04 GMT

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.

Re: Macro Question - Module Opening Post Reload

rwunderlich — Mon, 06 Aug 2012 15:39:36 GMT

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

Re: Macro Question - Module Opening Post Reload

Mon, 06 Aug 2012 16:00:51 GMT

Certainly worth the try.

Many thanks to all!

Re: Macro Question - Module Opening Post Reload

Tue, 07 Aug 2012 06:48:58 GMT

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