I need to read hundreds of Excel files to count and retrieve data only from of highlighted cells. I’ve tried the code below, but I encounter an OLE error when processing more than 60 files. Is there a better approach I could use, such as PowerShell, SQL, Python or another application?

The VBA CODE I used

Sub CountHighlightedCellsInFiles()
    Dim objExcel As Object
    Dim listFilePath As String
    Dim outputFilePath As String
    Dim fileNames As Variant
    Dim results As String
    Dim Workbook As Workbook
    Dim Worksheet As Worksheet
    Dim cell As Range
    Dim baseFileName As String
    Dim colorIndex As Long
    Dim val As Variant
    Dim fileNameParts As Variant
    Dim fileResults As String
    Dim sheetFound As Boolean
    
    ' Create Excel Application object
    Set objExcel = CreateObject("Excel.Application")
    objExcel.Visible = False  ' Run in the background
    objExcel.DisplayAlerts = False  ' Disable alerts
    
    ' Define the paths
    listFilePath = "C:UsersDocumentslist.txt"
    outputFilePath = "C:UsersDocumentsoutput.txt"
    
    ' Read file names from the text file
    fileNames = Split(CreateObject("Scripting.FileSystemObject").OpenTextFile(listFilePath).ReadAll, vbCrLf)
    
    ' Loop through each file name
    For Each Filename In fileNames
        On Error Resume Next  ' Start error handling
        Set Workbook = objExcel.Workbooks.Open(Filename)
        
        If Err.Number <> 0 Then  ' Check if an error occurred
            results = Filename & vbTab & "#N/A (Error Opening File)" & vbCrLf
            Err.Clear  ' Clear the error
            GoTo WriteResults  ' Skip to writing results
        End If
        
        ' Initialize temporary results for the current file
        fileResults = ""
        sheetFound = False
        
        ' Loop through each worksheet to find the one matching the pattern
        For Each Worksheet In Workbook.Worksheets
            If Worksheet.Name Like "Sheet1" Then  ' Adjust pattern as needed
                sheetFound = True
                
                ' Loop through each cell in the specified range
                For Each cell In Worksheet.Range("B3:AK60")
                    ' Check if the cell's interior color is not automatic (highlighted)
                    colorIndex = cell.Interior.colorIndex
                    If colorIndex <> -4142 Then  ' -4142 represents xlNone
                        ' Get the value of the highlighted cell
                        val = cell.Value
                        
                        ' Append the value to the file results string
                        If fileResults = "" Then
                            fileResults = val
                        Else
                            fileResults = fileResults & vbTab & val  ' Use vbTab for separation
                        End If
                    End If
                Next cell
                
                ' Exit the loop after processing the first matching sheet
                Exit For
            End If
        Next Worksheet
        
        ' Get the base file name without extension
        fileNameParts = Split(Filename, "")
        baseFileName = Left(fileNameParts(UBound(fileNameParts)), Len(fileNameParts(UBound(fileNameParts))) - 5)
        
        ' Combine the base file name with the accumulated values or #N/A
        If sheetFound Then
            If fileResults <> "" Then
                results = baseFileName & vbTab & fileResults & vbCrLf  ' Use vbTab for separation
            Else
                results = baseFileName & vbTab & "#N/A" & vbCrLf  ' Use #N/A when no highlighted cells
            End If
        Else
            results = baseFileName & vbTab & "#N/A (Sheet Not Found)" & vbCrLf
        End If
        
WriteResults:
        ' Write the results to the output text file after each file is processed
        Open outputFilePath For Append As #1
        Print #1, results
        Close #1
        
        ' Close the workbook without saving changes
        If Not Workbook Is Nothing Then
            Workbook.Close False
            Set Workbook = Nothing  ' Release the workbook object
        End If
        
        ' Allow Excel to process other events
        DoEvents
        
        ' Introduce a slight delay to reduce OLE errors
        Application.Wait Now + TimeValue("00:00:01")  ' Wait for 1 second
    Next Filename
    
    ' Quit the Excel application
    objExcel.Quit
    
    ' Release objects
    Set Worksheet = Nothing
    Set objExcel = Nothing
End Sub

I have a link to a zip file that includes ‘list.txt’ and ‘output.txt’ files, as well as a couple of Excel files for demonstration purposes to illustrate the issues I have encountered.
Hope this clears up the issues I’ve been facing.