macro hangs in for loop because QV cannot update table fast enough

avastani — Fri, 20 Nov 2009 21:59:53 GMT

I am using the code below to export portions of the table into different files. The table has over 20,000 rows with 50+ columns. When the code enters the for loop for the first time, it executes fine but when it enters the next go around, it stops at the Paste line saying it cannot export because there is no data. When I exit the macro window and go back to the application, surely the table is empty, but when I click the regions and then come back, the data is populated. When the data is blank it says, "Allocated Memory Exceeded" but in the Task Manager, I am only at 500MB ram consumption from a total of 2GB.

I wonder what can I do to make the exports work in the for loop to breakout by region i.e. a loop and reduce but not to a qvw but to an excel.


 
Sub


 exportList


























'Declare and set variable to master chart with all columns

Dim



 
Set



 
Dim



 
Set



 
Dim



 
Set




 lastDateStamp = ActiveDocument.Variables("varLastRefreshDate") lastDateStamp currentDateStamp = ActiveDocument.Variables("varCurrentRefreshDate") currentDateStamp exportedDataObject = ActiveDocument.GetSheetObject("CH01") exportedDataObject

Dim



 fileName
fileName = "C:\QlikView\Regions\weekly file "


Dim


 

 
Set



 XLApp = CreateObject("Excel.Application")

XLApp.Visible =
False


dim

 regions
set

 regions = ActiveDocument.Fields("Region").GetPossibleValues

For



 i =0 to regions.Count
'- 1
'msgbox(regions.Item(i).Text)
(i = regions.Count) then

XLApp.Workbooks.Open fileName & lastDateStamp.GetContent.
String & " Mgt.xls", True, False
Set

 XLWB = XLApp.Application.ActiveWorkbook

Set



 XLWS = XLWB.Worksheets("Combined")

ActiveDocument.Fields(
"Region").SelectAll

Set



 exportedDataObject = ActiveDocument.GetSheetObject("CH02")

exportedDataObject.CopyTableToClipboard
True

XLWS.Paste XLWS.Range(
"A1")

XLWB.SaveAs fileName & currentDateStamp.GetContent.
String & " Mgt.xls"


Else



XLApp.Workbooks.Open fileName & lastDateStamp.GetContent.
String & " Region " & regions.Item(i).Text & ".xls", True, False





Set




 XLWB = XLApp.Application.ActiveWorkbook



Set XLWS = XLWB.Worksheets("Combined")

ActiveDocument.Fields(
"Region"). Select(regions.Item(i).Text)

Set



 exportedDataObject = ActiveDocument.GetSheetObject("CH02")

exportedDataObject.CopyTableToClipboard
True

XLWS.Paste XLWS.Range(
"A1")

XLWB.SaveAs fileName & currentDateStamp.GetContent.
String & " Region" & regions.Item(i).Text & ".xls"

End

 If


XLWB.Close



ActiveDocument.Clear
Next

XLApp.Quit

Set

 XLApp = Nothing





Set
 


 XLWB = Nothing

Set

 XLWS = Nothing

macro hangs in for loop because QV cannot update table fast enough

Fri, 20 Nov 2009 22:36:20 GMT

Try adding the following line in your macro, it might be worthwhile adding this in after each function that takes a bit of time, this will force QVW to wait until it is free to continue:

ActiveDocument.GetApplication.WaitForIdle

macro hangs in for loop because QV cannot update table fast enough

avastani — Fri, 20 Nov 2009 23:47:19 GMT

Tried this, the macro ran for over 2 hours and just didn't produce a single Excel output. If I remove it though, the first one is created in less than 2 minutes. Goes to say that something is wrong with QV not refreshing the chart object properly?

topic macro hangs in for loop because QV cannot update table fast enough in QlikView

macro hangs in for loop because QV cannot update table fast enough

macro hangs in for loop because QV cannot update table fast enough

macro hangs in for loop because QV cannot update table fast enough