2 Replies Latest reply: Nov 20, 2009 6:47 PM by Amirali Vastani RSS

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

    Amirali Vastani

      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