VBA to search multiple PDF documents for String

[bradyboyy88]

Hi,

I have put together some code to search for some strings in a pdf. However, I am running into the issue where adobe crashes after the first document it searches. I am search through multiple document locations so I only wanted to create the adobe object once so I am not sure if i am using it correctly. I create the AcroExch.App and AcroExch.AVDoc objects. If it is a pdf file it passes the AcroExch.AVDoc object the search function. Once it searches that file then it closes the AcroExch.AVDoc. This is where I think I may be breaking the code. Does closing terminate that AcroExch.AVDoc object or should i keep it open? Please see below:

sub test

        'create pdf object
        Dim PDFObj As Object, AVDocObj As Object
        Set PDFObj = CreateObject("AcroExch.App")
        Set AVDocObj = CreateObject("AcroExch.AVDoc")


        While Not ActiveRecordset.EOF
                    Select Case LCase(Right(ActiveRecordset.fields("DOCUMENT_LOCATION").value, 4))
                        Case Is = "docx", ".doc", "docm", "dotm"
                            WordsFound = SearchDocument(WordApp, MainPathDirectory & ActiveRecordset.fields("DOCUMENT_LOCATION").value, QandA_EXP_Array)
                        Case Is = ".pdf"
                            WordsFound = SearchDocument(AVDocObj, MainPathDirectory & ActiveRecordset.fields("DOCUMENT_LOCATION").value, QandA_EXP_Array)
                        Case Is = "xlsx", "xlsm", ".xls", ".xlm"
                            WordsFound = SearchDocument(ExcelApp, MainPathDirectory & ActiveRecordset.fields("DOCUMENT_LOCATION").value, QandA_EXP_Array)
                    End Select
        ActiveRecordset.MoveNext
        Wend

        PDFObj.Exit
End Sub


Function SearchDocument(ByRef AppObject As Object, FilePath_Export As String, WordsToFind() As String) As String
    
Dim lCounter As Long
Dim WordsFound As String
    
On Error GoTo ErrorHandler

Select Case LCase(Right(FilePath_Export, 4))
            
    Case Is = "docx", ".doc", "docm", "dotm"  'Check if word document
    
        Dim WordDoc As Word.Document
        
        Set WordDoc = AppObject.Documents.Open(FileName:=FilePath_Export, ReadOnly:=True)
                    
        WordDoc.ActiveWindow.View.ReadingLayout = False
                                           
        'Select all and Check if word exists
        AppObject.Selection.WholeStory
        AppObject.Selection.Find.ClearFormatting
                        
        'loops through all possible search words in the keywords to search for and tries to find them in the word document
        
        For lCounter = 0 To UBound(WordsToFind)
            With AppObject.Selection.Find
                .Text = WordsToFind(lCounter)
                .Forward = True
                .Wrap = 1
                .Format = False
                .MatchCase = False
                .MatchWholeWord = True
                .MatchWildcards = False
                .MatchSoundsLike = False
                .MatchAllWordForms = False
                            
                If .Execute Then
                    'Adds the word to the collection of words found which will later be printed on sheet
                    WordsFound = WordsFound & WordsToFind(lCounter)
                End If
            End With
        Next
        
        SearchDocument = WordsFound
        WordDoc.Close False
    
    Case Is = ".pdf"  'Check if pdf document
              
        If AppObject.Open(FilePath_Export, "") = True Then
            For lCounter = 0 To UBound(WordsToFind)
                If AppObject.findtext(WordsToFind(lCounter), False, False, False) = True Then
                    WordsFound = WordsFound & WordsToFind(lCounter)
                End If
            Next
        End If
        
        AppObject.Close True
        SearchDocument = WordsFound

    Case Is = "xlsx", "xlsm", ".xls", ".xlm"
        
        Dim ExcelWbk As Excel.Workbook
        Dim SearchArea As Range
        
        Set ExcelWbk = AppObject.Workbooks.Open(FileName:=FilePath_Export, ReadOnly:=True)
                        
        'loops through all possible search words in the keywords to search for and tries to find them in the word document
        
        For lCounter = 0 To UBound(WordsToFind)
            Set SearchArea = Cells.Find(What:=lCounter, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
            If Not SearchArea Is Nothing Then
                'Adds the word to the collection of words found which will later be printed on sheet
                WordsFound = WordsFound & WordsToFind(lCounter)
            End If
        Next
        
        SearchDocument = WordsFound
        ExcelWbk.Close False
                             
                             
End Select
    
Exit Function
ErrorHandler:

    Debug.Print Err.Description
    SearchDocument = "Error"
    
End Function