Me and my colleagues have created around 14.000 copies of a certain workbook in the last 8 years or so. Pretty simple lightweight workbook, few userforms, some code, 2 sheets with a variable amount of named ranges (on average maybe 30 or so). Nothing special. Now they have asked me to scan through all of these workbooks finding and listing certain values. I haven written and tested a code written in a new workbook that searches for all those workbooks on our network, opens them, scans for all the named ranges in them, gets the values needed and closes the workbook without saving. Works like a charm . .. … if only could run it for a few hours straight.

After having opened around 1000 workbooks Excel throws an automation error. So I already build in a sort of autosave and restart log at certain intervals, so if the thing crashes I only lose maybe 50 results. But having to restart the thing 15 times or so just isn’t very productive. After closing a workbook I’m already setting all the used variables to Nothing. Except for the opened workbook itself, I can’t seem to make Excel fet about all the copies it already opened. They stay in the project overview. Perhaps this is what causes the problem?

Srceenshot of all workbooks in project overview with 'automation error exception message'

This is a short version of the code:

Sub SearchAllLogs()

'General stuff
    Dim ValuesWs As Worksheet
    Set ValuesWs = ThisWorkbook.Worksheets("Searchvalues")
        
    Dim ResultsWs As Worksheet
    Set ResultsWs = ThisWorkbook.Worksheets("FoundResults")
    
    Dim LogboekWb As Workbook
    'set later
    Dim LogboekWs As Worksheet
    'set later

    locationNetwork =  "W:\something\something" & "\"
    filenNm = "Logboek"
    fileExt = ".xlsm"

' Making a collection of values to search for.
    Dim Coll_SearchValues As Collection
    Set Coll_SearchValues = New Collection

    ' - here I .add stuff to the Collection

    folderNmbrStart = 12356
    folderNmbrEnd = 25468

' Searching through all the workbooks
    For folderNmbr =  folderNmbrStart To folderNmbrEnd
        
        lgbFile = locationNetwork & folderNmbr & "\" & filenNm & fileExt
         
        'check if Wb really exists
        foundIt = false   
        If Not Dir(lgbFile) = "" Then foundIt = True
        
        'if it exists
        If foundIt = True Then 

            Application.EnableEvents = False
            Workbooks.Open Filename:=lgbfile, ReadOnly:=True
            Application.EnableEvents = True
                
            Set LogboekWb = ActiveWorkbook
            Set LogboekWs = LogboekWb.Worksheets("TargetSheet")
    
            With LogboekWs
               
                'here I loop through all the Name.Name in LogboekWs.Names
                'looking for the right nmdRanges
                'get the all the values needed from those ranges and copy them to the ResultsWs
            
            End With
    
            'closing workbook
            Workbooks(LogboekWb.Name).Close Savechanges:=False
    
        End If

        'Setting the workbooks and worksheets to nothing (I hoped this would help)
        Set LogboekWb = Nothing
        Set LogboekWs = Nothing

    Next folderNmbr

End Sub

Me and my colleagues have created around 14.000 copies of a certain workbook in the last 8 years or so. Pretty simple lightweight workbook, few userforms, some code, 2 sheets with a variable amount of named ranges (on average maybe 30 or so). Nothing special. Now they have asked me to scan through all of these workbooks finding and listing certain values. I haven written and tested a code written in a new workbook that searches for all those workbooks on our network, opens them, scans for all the named ranges in them, gets the values needed and closes the workbook without saving. Works like a charm . .. … if only could run it for a few hours straight.

After having opened around 1000 workbooks Excel throws an automation error. So I already build in a sort of autosave and restart log at certain intervals, so if the thing crashes I only lose maybe 50 results. But having to restart the thing 15 times or so just isn’t very productive. After closing a workbook I’m already setting all the used variables to Nothing. Except for the opened workbook itself, I can’t seem to make Excel fet about all the copies it already opened. They stay in the project overview. Perhaps this is what causes the problem?

Srceenshot of all workbooks in project overview with 'automation error exception message'

This is a short version of the code:

Sub SearchAllLogs()

'General stuff
    Dim ValuesWs As Worksheet
    Set ValuesWs = ThisWorkbook.Worksheets("Searchvalues")
        
    Dim ResultsWs As Worksheet
    Set ResultsWs = ThisWorkbook.Worksheets("FoundResults")
    
    Dim LogboekWb As Workbook
    'set later
    Dim LogboekWs As Worksheet
    'set later

    locationNetwork =  "W:\something\something" & "\"
    filenNm = "Logboek"
    fileExt = ".xlsm"

' Making a collection of values to search for.
    Dim Coll_SearchValues As Collection
    Set Coll_SearchValues = New Collection

    ' - here I .add stuff to the Collection

    folderNmbrStart = 12356
    folderNmbrEnd = 25468

' Searching through all the workbooks
    For folderNmbr =  folderNmbrStart To folderNmbrEnd
        
        lgbFile = locationNetwork & folderNmbr & "\" & filenNm & fileExt
         
        'check if Wb really exists
        foundIt = false   
        If Not Dir(lgbFile) = "" Then foundIt = True
        
        'if it exists
        If foundIt = True Then 

            Application.EnableEvents = False
            Workbooks.Open Filename:=lgbfile, ReadOnly:=True
            Application.EnableEvents = True
                
            Set LogboekWb = ActiveWorkbook
            Set LogboekWs = LogboekWb.Worksheets("TargetSheet")
    
            With LogboekWs
               
                'here I loop through all the Name.Name in LogboekWs.Names
                'looking for the right nmdRanges
                'get the all the values needed from those ranges and copy them to the ResultsWs
            
            End With
    
            'closing workbook
            Workbooks(LogboekWb.Name).Close Savechanges:=False
    
        End If

        'Setting the workbooks and worksheets to nothing (I hoped this would help)
        Set LogboekWb = Nothing
        Set LogboekWs = Nothing

    Next folderNmbr

End Sub

• excel
• set

• You could try adding something like DoEvents and Application.CutCopyMode = False after your garbage collection Set LogboekWs = Nothing – Michal Commented Mar 28 at 2:00

1 Answer 1

This is a little cleaner (doesn't rely on Activeworkbook):

 Dim xlApp as excel.Application, ai As AddIn

 Set xLApp = New Excel.Application
 xLApp.EnableEvents = False
 xLApp.Visible = True ' you can make this instance invisible (false) once you are satisfied with the way it works.
 xLApp.DisplayAlerts = False
 xLApp.EnableAnimations = False
 xLApp.AutomationSecurity = msoAutomationSecurityForceDisable
 For Each ai In pXLApp.AddIns
     ai.Installed = False
 Next ai
 For Each ai In pXLApp.AddIns2
     ai.Installed = False
 Next ai

 ... stuff; then your for loop 
 'check if Wb really exists
        If Dir(lgbFile) <> "" Then 

            set LogboekWb=xlApp.Workbooks.Open( Filename:=lgbfile, ReadOnly:=True, AddToMru:=false, UpdateLinks:=0)
                
            With LogboekWb.Worksheets("TargetSheet")
               
                'here I loop through all the Name.Name in LogboekWs.Names
                'looking for the right nmdRanges
                'get the all the values needed from those ranges and copy them to the ResultsWs
            
            End With
    
            'closing workbook
            LogboekWb.Close Savechanges:=False
            set LogboekWb=nothing
    
        End If
Next ...

xLApp.Quit
Set xLApp = Nothing

This creates a separate EXCEL instance (set xlApp=new EXCEL.Application); and open them there, this keeps Thisworkbook's memory cleaner.