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.
//Create instance of Excel
Path = "C:\temp\"
FileName = "Test_"
strSaveFile = Path & fileName
set oXL = CreateObject("Excel.Application")
oXL.visible = True
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
oSH.Name = "Data"
oXL.ActiveWorkBook.SaveAs strSaveFile & replace(date, "/", "-") & ".xlsx"
Set oSH = Nothing
Set oXL = Nothing
Message was edited by: Michael Castillo Edited to better present the issue.
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.
Regarding to the cache you could try:
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.
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.
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?
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.
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.
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.