Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi
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
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?