Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Guidok
Contributor II
Contributor II

Macro doesn't work on postreload

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

 

1 Solution

Accepted Solutions
marcus_sommer

The index in Excel is different to QV because there is no row/column = 0 else it starts with 1. Therefore add:

...
objExcel.Worksheets(1).cells(RowIter + 1,ColIter + 1).value = cell.Text
...

- Marcus

View solution in original post

35 Replies
Guidok
Contributor II
Contributor II
Author

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

marcus_sommer

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

- Marcus

Guidok
Contributor II
Contributor II
Author

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

marcus_sommer

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

Guidok
Contributor II
Contributor II
Author

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

Guidok
Contributor II
Contributor II
Author

I don't get any error messages.

marcus_sommer

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

Guidok
Contributor II
Contributor II
Author

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.

marcus_sommer

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