topic Macro doesn't work on postreload in QlikView

Macro doesn't work on postreload

Guidok — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I've a macro for exporting an excel file which works fine when I run the script manually.

When I add it to a postreload trigger, it doesn't.

I've checked the document properties -- security --> Export allowed

Also under user preferences --> security --> Module and File are checked

Any idea suggestions why the macro doesn't seem to work as a postreload?

Regards,

Guido

Re: Macro doesn't work on postreload

Guidok — Wed, 23 Oct 2019 08:55:06 GMT

I've tried this too:

1.Create Batch file ==> "C:\Program Files\QlikView\qv.exe" /l "path_to_document"

2.Create Task Scheduler to RUN Batch file (1)

3.QVW Document ==> Document properties > OnPostReload ==> Add RUN MACRO

4.add (end of script)

ActiveDocument.Save

Application.Quit

But it still doesn't work

Re: Macro doesn't work on postreload

marcus_sommer — Wed, 23 Oct 2019 15:25:14 GMT

Maybe this: Reload-with-batfile-and-macro and QlikView-automation-no-more-working-in-new-server are helpful in any way.

- Marcus

Re: Macro doesn't work on postreload

Guidok — Thu, 24 Oct 2019 09:34:53 GMT

Hi Marcus, I'm working on a laptop... I'm using this script in a batchfile:

@echo off
"C:\Program Files\QlikView\Qv.exe" /r "C:\Users\kakeg\Desktop\Qlikview\Orderverzamelregels.qvw"

I've tried /l but that didn't help either.

Regards,

Guido

Re: Macro doesn't work on postreload

marcus_sommer — Thu, 24 Oct 2019 12:34:29 GMT

Please elaborate it more detailed what you did and what worked and what don't worked - means also whered id it break and also if there are any error-messages.

- Marcus

Re: Macro doesn't work on postreload

Guidok — Thu, 24 Oct 2019 13:24:01 GMT

Hi, i've tried different security settings.

The reload works, but the macro for the export doesn't work when I use a postreload trigger.

When I trigger the macro manually it creates the excel file.

I tried adding the lines below to the macro:

ActiveDocument.Save
Application.Quit

But that didn't help either.

When I understand it correctly, the /r only does the reload and no triggers go off.

What I want is that the script in the bat file:

1) Reloads the QV document

2) New excel file is generated

1 works but 2 doesn't...

Re: Macro doesn't work on postreload

Guidok — Thu, 24 Oct 2019 13:24:22 GMT

I don't get any error messages.

Re: Macro doesn't work on postreload

marcus_sommer — Thu, 24 Oct 2019 14:59:39 GMT

The quit-statement isn't quite right - it should be:

ActiveDocument.GetApplication.Quit

The /r parameter won't work but with /l it should - important is also how the batch is triggered and with which user. What happens if you execute the batch manually?

- Marcus

Re: Macro doesn't work on postreload

Guidok — Thu, 24 Oct 2019 15:10:01 GMT

I've changed the script to this:

@echo off
"C:\Program Files\QlikView\Qv.exe" /l "C:\Users\kakeg\Desktop\Qlikview\Orderverzamelregels.qvw"

I've the post reload trigger active.

This is the macro:

sub TePickenRegels
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
objExcel.DisplayAlerts = False
Set xlDoc = objExcel.Workbooks.Add
set obj = ActiveDocument.GetSheetObject("CH01")
obj.CopyTableToClipboard true
objExcel.Range("A1").PasteSpecial
objExcel.Worksheets(1).Rows("1:1000").RowHeight = 15
objExcel.Worksheets(1).Columns("A:D").Autofit
'objExcel.Worksheets(1).Columns(2).ColumnWidth = 25
'objExcel.Worksheets(1).Columns(3).ColumnWidth = 25
xlDoc.SaveAs "C:\Users\kakeg\Desktop\Qlikview\Exports\TePickenRegels.xlsx"
xlDoc.Close
ActiveDocument.Save
ActiveDocument.GetApplication.Quit
'Application.Quit
end sub

I've run the script manually. It does the reload but no excel file is exported. When I run the macro manually it does make an export.

Re: Macro doesn't work on postreload

marcus_sommer — Thu, 24 Oct 2019 15:35:32 GMT

At first I would check if any macro runs by just using msgbox's like:

sub x
msgbox "start"
msgbox "mid"
msgbox "end"
end sub

If they run I would them place into the routine to find the point where it breaks. Further I suggest not to set the visibility and the alerts to FALSE else I would like to see what happens - if the whole worked I could switch them off again.

- Marcus

Re: Macro doesn't work on postreload

Guidok — Fri, 25 Oct 2019 06:46:56 GMT

I've added this as the first macro:

sub x
msgbox "start"
msgbox "mid"
msgbox "end"
end sub

and then ran the script. I didn't get any messages so it seems like the macro's don't run at all.

Re: Macro doesn't work on postreload

marcus_sommer — Fri, 25 Oct 2019 07:34:03 GMT

Take another look to the above provided links to the there mentioned security settings.

- Marcus

Re: Macro doesn't work on postreload

Guidok — Fri, 25 Oct 2019 08:18:09 GMT

I think these should be the settings. It's in Dutch, but it's Exports allowed and Macro security cancelled:

And Module and File are allowed.

Re: Macro doesn't work on postreload

marcus_sommer — Fri, 25 Oct 2019 09:01:22 GMT

By the user-properties enable all unless the last one.

Re: Macro doesn't work on postreload

Guidok — Fri, 25 Oct 2019 09:20:21 GMT

I've changed the settings but still no excel export.

Re: Macro doesn't work on postreload

marcus_sommer — Fri, 25 Oct 2019 10:45:20 GMT

Just to be sure - no Excel and QV is running, means no open files or any other instances of the tools could impact the task. Then you starts the batch-file manually - what happens? Open + reload then will be a msgbox displayed?

- Marcus

Re: Macro doesn't work on postreload

Guidok — Fri, 25 Oct 2019 11:12:50 GMT

Excel and QV are closed. I run the bat file and a cmd window opens, the window is empty.

Then QV opens and the reload starts.

That's it. QV stays open and the cmd window is still empty.

Re: Macro doesn't work on postreload

marcus_sommer — Fri, 25 Oct 2019 11:26:49 GMT

The within the OnPostReload trigger specified macro is the correct one - also from the spelling?

To exclude that anything prevents the displaying of the msgbox popup's (they might be also hiden or outside of the screen) change/add to a selection-statement like:

ActiveDocument.Fields("Year").Select 2019

or something similar - you should now see the new set selection.

- Marcus

Re: Macro doesn't work on postreload

Guidok — Fri, 25 Oct 2019 12:35:23 GMT

I have only 1 macro in the whole document.

When I run it manually, I see the message.

I've added a filter in the statement like you suggested, but it doesn't change when I run it with the script. It does change when I run the macro manually.

Re: Macro doesn't work on postreload

marcus_sommer — Fri, 25 Oct 2019 12:44:51 GMT

Some setting prevents the macro-execution. Within the script-editor in the bottom left area there are multiple buttons like Data, Functions, Variables and Properties - within the properties are also options for the execution of external programs.

Another trial would be to remove (with backup) or a renaming of the Qlikview settings.ini in:

C:\Users\YourUser\AppData\Roaming\QlikTech\QlikView

and now you should be asked if you want to execute any macros.

- Marcus