Excel out of memory

DG_Montana

New Member
Joined
Dec 27, 2011
Messages
19
I've created a VBA process that opens an Excel template file, populates it with data from a server, saves, prints and closes the file. This process works flawlessly when executed from a menu by a user, but I have been asked to automate it to create a series of reports. To do this, I open a file that contains the list of geographic regions that required reports (there are about 3100 geographic regions that need reports created). The automated process creates, saves and prints one report in just over a minute, but the process dies with an out of memory error during the processing of the 61st report. It doesn't seem to matter which geographic region I begin with, it always runs fine until the 61st geography in the list and then throws an out of memory error.

The out of memory error occurs in one of several different subroutines, so it seems to be unrelated to a specific line of my VBA code. This process has been run on an old laptop with just 1GB of memory (XP & Excel 2007) and also on a fairly new desktop PC with 6GB of memory (64-bit Windows 7 and Excel 2007), but it fails at the 61st report on both machines. Does anyone know of some limitation in Excel that might be causing this, and of a work-around? Thanks!
 

jimrward

Well-known Member
Joined
Feb 24, 2003
Messages
1,708
Office Version
2016, 2013, 2011, 2010, 2007
Platform
Windows
Show the code you have thus far if you can
 

DG_Montana

New Member
Joined
Dec 27, 2011
Messages
19
The report-building process is contained in a 1MB Excel add-in file, which is called from the code below. The code below shows my attempt to save, print, and then close the report. Any suggestions for improvements are most welcome.
Code:
    ' Redefine the arrGeogs array to only allow one county at a time to be processed

    ReDim arrCurrGeog(0, 5)
    
    ' Populate the arrCurrGeog array with the current geography
    
   For i = LBound(arrGeogs) To UBound(arrGeogs)
        
    ' Add the current county in the arrGeogs array to the arrCurrGeog array
        
        arrCurrGeog(0, 0) = arrGeogs(i, 0)
        arrCurrGeog(0, 1) = arrGeogs(i, 1)
        arrCurrGeog(0, 2) = arrGeogs(i, 2)
        arrCurrGeog(0, 3) = arrGeogs(i, 3)
        arrCurrGeog(0, 4) = arrGeogs(i, 4)
    
        ' Build the requested reports.
    
        If Not Application.Run("'EPS-HDT.xla'!BuildReports", _
            arrCurrGeog, arrRpts, strRegionName, sSOURCE, _
            arrStatesCounties, arrDataMessages) Then
            Err.Raise glHANDLED_ERROR
        End If
    
        ' Set a pointer to the workbook that has just been generated
        
        Set wbNewRpt = Nothing
        For Each wb In Application.Workbooks
            
            On Error Resume Next ' don't error if the workbook doesn't contain a calcs sheet
            sCurrentFIPS = wb.Sheets("Calcs").Range("Geog").Offset(1, 0).Value
            On Error GoTo ErrorHandler
            
            If sCurrentFIPS = arrCurrGeog(0, 2) Then
                Set wbNewRpt = wb
                Exit For
            End If
        Next wb
        
        ' Verify that the new workbook has been located and the pointer set
        ' If the new workbook cannot be found, skip to the next FIPS code
        ' and continue processing
        
        If wbNewRpt Is Nothing Then
            ThisWorkbook.Names("ErrFIPS").RefersToRange.Offset(lErrs, 0).Value = arrCurrGeog(0, 2)
            lErrs = lErrs + 1
            GoTo SkipToNextFIPS
        End If
    
        ' Append a leading '0' in front of the FIPS code to force it to be 5 digits, if necessary
        
        If Len(arrCurrGeog(0, 2)) < 5 Then
            arrCurrGeog(0, 2) = "0" & arrCurrGeog(0, 2)
        End If
        
        ' Save the report into the current working directory, but don't error if the
        ' file exists and the user chooses not to replace it.
        
        On Error Resume Next
        wbNewRpt.SaveAs Filename:=arrCurrGeog(0, 2) & " - " & arrCurrGeog(0, 0) & " " & arrCurrGeog(0, 3) & " Measures"
        On Error GoTo ErrorHandler
        
        ' Print the report
        
        wbNewRpt.Activate
        If Not SetupMeasuresPrint() Then
            Err.Raise glHANDLED_ERROR
        End If
        
        ' Print the workbook with the study guide pages

        ActiveWorkbook.PrintOut ActivePrinter:=sSelectedPrinter
            
        ' Close the report
        
        wbNewRpt.Close SaveChanges:=True
    
        ' Check to see if the user wants to continue

        If i <> 0 And Fix(i / 200) = i / 200 Then
            Response = MsgBox("Do you want to create Measures reports for the next 200 geographic regions?", vbYesNo, "EPS-HDT")
            If Response = vbNo Then
                GoTo ErrorExit
            End If
        End If

SkipToNextFIPS:

        Next i
 

DG_Montana

New Member
Joined
Dec 27, 2011
Messages
19
Please let me know if it would be useful to have the exact error message given by Excel when it runs out of memory. Thanks again for any and all suggestions!
 

Forum statistics

Threads
1,082,151
Messages
5,363,430
Members
400,736
Latest member
Aida

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top