QlikView hangs after Macro

[johngouws]

Good day,
I would like to modify a macro to send a straight table data to a excel file rather than a csv file. The original, csv export has been working for a few years from what I can see.
In the below macro, the portion of the macro that creates the excel file in the correct folder works perfectly.

The problem comes in when I want to Quit QlikView (ActiveDocument.GetApplication.Quit). When I un-comment that statement and run the macro, the excel file is created but then QlikView hangs with the below error.
I appreciate your assistance with this. Thank you in advance.

sub Reorder_Report

'Reading the file save path:
set v = ActiveDocument.Variables("vPath")
vPath = v.GetContent.String

'Define the file name:
set v = ActiveDocument.Variables("vFileName")
vFileName = v.GetContent.String

'Add the reload timestamp:
SET v = ActiveDocument.Variables("vfiledate")
varDate = v.GetContent.STRING

'Initialize Excel object:
set XLApp = CreateObject("Excel.Application")

XLApp.Visible = true

'Add a Workbook to Excel file:
set XLDoc = XLApp.Workbooks.Add

set XLSheet = XLDoc.Worksheets(1)
XLSheet.Activate

set table=ActiveDocument.GetSheetObject("CH04")

'Copy content of this object:
table.CopyTableToClipboard true

'And paste into Excel
XLSheet.Paste

XLSheet.Name = "Re-Order Report"

set XLSheet = XLDoc.Worksheets(1)
XLSheet.Activate

'Define a Range for which formatting will happen
set TitleRange = XLSheet.Range("A1:P1")
TitleRange.Font.Size = 12
TitleRange.RowHeight = 20

set DataRange = XLSheet.Range("A2:P300")
DataRange.Font.Size = 10
DataRange.RowHeight = 15

Const xlCenter = -4108
TitleRange.HorizontalAlignment = xlCenter
TitleRange.VerticalAlignment = xlCenter

XLSheet.Range("A1").Select

XLDoc.SaveAs vPath+vFileName+varDate

XLDoc.close

XLApp.Quit

' Up to here the xlsx file is created in the folder
' As soon as I un-comment the below Quit, QV hangs.


' ActiveDocument.GetApplication.Quit

end sub

[m_woolf]

I ran your script (stripped down) in 12.4 SR1 with no errors.

Maybe put ActiveDocument.GetApplication.WaitForIdle before the Quit command?

[marcus_sommer]

Maybe there is any unsaved state within the QV or something similar which usually results in a popup querying if you want to save the app and the quit-statement ignored it - but windows comes in conflict with it and breaks. Therefore I suggest to check if a save- and/or a closedoc-statement would be helpful. If not it may be more difficult to find the cause - maybe a msgbox err.number with or without an on error resume next might provide further hints.

- Marcus

[johngouws]

Thanks for trying it out. 

I have tried the ActiveDocument.GetApplication.WaitForIdle but it gave the same result. Also previously tried 'wait' for 120 seconds, with same result. 

I am not sure if this could be it, but, after I am had to close QV, and then go to notepad, and do a ctrl+v, the entire report's data copies. Do you know how to 'clear' this before trying to do the ActiveDocument.GetApplication.Quit ? 

Thanks a lot

[marcus_sommer]

I don't think that's really the clipboard which caused the issue - but nevertheless you could try to clear it. Within the following post is an example for it: Macro-doesn-t-work-on-postreload.

If you say wait for 120 seconds you mean a sleep-statement? Because AFAIK the WaitForIdle has no further parameter to wait a certain time. In general are WaitForIdle and Sleep useful to ensure that no step runs before the previous ones are finished.

Did you try my suggestion with a save- and close-statement? How is this macro triggered - directly per hitting a button or with any action, like an OnPostReload? With such an action there would be an unsaved state. Other things which might be conflicting by just killing the process could be the compressing of the qvw, auto-backup settings, using of prj-folder and probably some more.

- Marcus

[johngouws]

Thanks a lot for the feedback. 

It uses a trigger OnOpen to check if the report must be emailed. 

I also tried the solution in your reply and I think I found an issue. The solutions all work on my local machine, which is 11.12 SR12 on a windows 10 machine. When I copy the same qvw to the server which has Windows Server 2008 R2 it writes the xlsx file to the folder but 'hangs' on the ActiveDocument.GetApplication.Quit. 

If I follow the process, the excel side and the email process completes. The QV Quit is what fails.

XLDoc.SaveAs vPath+vFileName+varDate '//// COMPLETES ////

'Close Workbook
XLDoc.close '//// COMPLETES ////

'Close whole Excel file
XLApp.Quit '//// COMPLETES ////

Call Mail_Reorder_Report '//// COMPLETES sending email ////

ActiveDocument.GetApplication.Quit '//// HANGS ////

End Sub

I think this may be a 2008 R2 issue?

[marcus_sommer]

I used the same several years with server 2008 r2 without such problem. Therefore I don't think that there is a general problem with it. More likely is that any user settings between your local machine and the server are different and/or that they run with a different release.

What does happens if you uncomment the mail-call? Maybe there is something which corrupts the ActiveDocument in any way. Which result do you get by:

msgbox(ActiveDocument.GetApplication.GetProcessId)

instead of the quit-statement? If there is a valid result you may use a hard-kill instead of the quit - see here what is meant: How-to-create-kill-QV-task. But before just consider my previous suggestion of saving and closing the app before terminating the instance.

- Marcus

[johngouws · Accepted solution]

With the 'call' commented out, the error is still the same. 

I really appreciate all your effort, but I cannot take any more of your time - thank you very much. 

Sending the data as a csv file works 100%, so I think that is the solution to stay with for now. 

Thank you very much. 

[Brett_Bleess]

Just wanted to add a final post on this one, my best hunch would be software defect in the release you are running on the server, so if the server is not running the same build as your Desktop client, that would be a possibility, but even if they are the same version, sometimes we can get something server-side that does not happen client-side as well.  Given the other poster got things to work fine in 12.40 track, that would be my best guess as to root cause of things on this one, but I marked your post as the workaround/solution...

Regards,
Brett