Re: Script Never Terminates - Paste Data to Excel - Qlik Community

Report Inappropriate Content · ‎2017-07-05

I'm modifying the original content of this thread to better present the issue.

I am attempting to export data for a list (array) of objects to Excel. The problem is the below script never completes. All objects up to CH317 are exported to Excel, then the Edit Module window closes and the bottom left corner of the QVW says "Macro Running..." indefinitely and stops responding (cannot click menus or do anything within the QVW).

I'm suspicious of memory issues with attempting to export so many objects. I'd like to attempt clearing the clipboard after every paste operation, but am unaware of a method to do just that or if that's an appropriate solution.

Script:

sub hardCode

//Create instance of Excel

Path = "C:\temp\"

FileName = "Test_"

strSaveFile = Path & fileName

set oXL = CreateObject("Excel.Application")

oXL.visible = True

oXL.Workbooks.Add

aSheetObj=Array("CH34","CH43","CH64","CH67","CH74","CH75","CH79","CH100","CH102","CH116","CH170","CH198", _

"CH202","CH204","CH205","CH206","CH207","CH220","CH221","CH222","CH223","CH224","CH225","CH237", _

"CH241","CH295","CH316","CH317","CH334","CH336","CH337","CH340","CH341","CH342","CH343","CH345", _

"CH346","CH348","CH349","CH351","CH353","CH354","CH358","CH369","CH371","CH372","CH373","CH377", _

"CH378","CH385","CH386","CH387","CH393","CH394","CH395","CH396","CH397","CH398","CH399","CH400", _

"CH406","CH422","CH423","CH425","CH426","CH427","CH458","CH462")

aCostType=Array("A","B")

For i=0 to UBound(aSheetObj) //Loop for all ObjectIDs within aSheetObj

For j = 0 to 1 //Loop through the CostType values

Set v = ActiveDocument.Variables("CostType") //Setting the CostType variable

v.SetContent aCostType(j), true

Set oSH = oXL.ActiveSheet

num_rows = oSH.UsedRange.Rows.Count //Determines number of used rows in Excel

oSH.Range("A" & num_rows+4).Select

Set obj = ActiveDocument.GetSheetObject(aSheetObj(i)) //Retrieve object

obj.CopyTableToClipboard True

oSH.Paste

sCaption=obj.GetCaption.Name.v

Set obj=Nothing

oSH.Range("A1").Select

oSH.Name = "Data"

oXL.ActiveWorkBook.SaveAs strSaveFile & replace(date, "/", "-") & ".xlsx"

Set oSH = Nothing

Set oXL = Nothing

End Sub

Message was edited by: Michael Castillo Edited to better present the issue.

rwunderlich · ‎2017-07-05

ObjectId is already a string, you should not add quotes when storing in the Array.

No: objectsToExcel(objectCounter) = Chr(34) & objectID & Chr(34)

Yes: objectsToExcel(objectCounter) = objectID

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Report Inappropriate Content · ‎2017-07-05

Sorry, that was left over from troubleshooting and was not part of the original code. The issue is different without Chr(34); I had tried many different things and lost track of what was what.

What happens with objectsToExcel(objectCounter) = objectID, is the macro never completes; the bottom left of the QVW continuously says "Macro Running..." and I have to stop the Qv.exe process. I'll update the main post for accuracy.

Report Inappropriate Content · ‎2017-09-06

I haven't spent enough time here to know if 'bumping' is a thing, but in case it is, ttt

marcus_sommer · ‎2017-09-06

Regarding to the cache you could try:

ActiveDocument.ClearCache

Beside them I would add a WaitForIdle and maybe also a Sleep statement to ensure that there no conflicts if anything isn't finished while the next statement is executed. Further I would for testings reduce the number of objects within the array, uncommenting some parts and maybe also using some msgbox for the loop-counter and other variables and/or writing them into a separate excel-sheet - and I think the problem will quite soon pop up.

- Marcus

Report Inappropriate Content · ‎2017-09-06

Thanks for the suggestions Marcus. I placed the ActiveDocument.ClearCache statement before the inner most Next on line 34 and executed the macro. Unfortunately, the qvw was met with the same behavior; it locked up. I checked my clipboard and it was full of data from each copied object. Is there a more appropriate line for ClearCache?

The script works with a small number of objects in the array, e.g. 5, however there isn't much benefit in having many small exports for my use case.

Report Inappropriate Content · ‎2017-09-06

Okay, I reduced the number of objects within the array to what is on a single sheet (7). The script made it through 6 objects, but locked up when it got to the 7th. I looked to see what was different about the 7th chart and it's the only one with a Drill Group.

I then removed the Drill Group from the chart's Dimensions, executed the script, and all objects in the array were exported. I suspect that the script doesn't "know" which level of the Drill Group to export. So, the question I'm looking into now is, how do I set the Drill Group level or pass a value for it prior to attempting to export?

marcus_sommer · ‎2017-09-07

Problems with the clipboard couldn't be resolved with the qlik cache/RAM handling but I think one of the suggestions here: Re: Sometimes. Macro paste method failed or incorrect format. Clipboard? will be quite helpful.

- Marcus

Report Inappropriate Content · ‎2017-09-08

Well, I thought charts that had a Drill Group configured were the problem, but the Edit Module still disappears and the qvw still locks up before it gets to an object with a Drill Group. The behavior is inconsistent and very frustrating to debug.

marcus_sommer · ‎2017-09-09

I think the issue is rather not caused from qlikview else from a buggy behaviour anywhere within the communication between vbs (qlik used just windows dll's), excel, the clipboard and the OS whereby the mainparts will probably be by the clipboard and the OS. For any reasons the clipboard didn't delete earlier data/objects - probably by some kind of locking it - and hit therefore at a certain point some limitations and freezed completely.

The reason why I think it goes rather in the above mentioned direction is that I experienced similar things without qlik just by using vba with excel/access and I have already seen more strange results by mixing up multiple different copies of data when they are pasted into the target. I'm not a specialist to this kind of computing but I think that there are situations where the indexing of the clipboard is disrupted and I could imagine that they will be caused by other processes which access the clipboard at the same time.

If you didn't know someone who is quite experienced to this kind of problems maybe by tracking down all processes with some sophisticated process monitor tools - you shouldn't really invest much time here else to develop any workaround. Some of them were mentioned within the link above and there are a lot more things possible which might bypass the problem.

For example, one way which I could imagine is to split your routine by exporting the first 5 objects, then saving the excel-file, closing the excel-instance, creating a new excel instance and reopening your excel and continuing the export with the next 5 objects and so on.

- Marcus