Excel out of memory

[DG_Montana]

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]

Show the code you have thus far if you can

 

[daverunt]

Are you saving then closing the reports once printed?

 

[DG_Montana]

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.

    ' 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]

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!