Option Explicit
Sub FindTextInPDF()
'Declaring the necessary variables.
Dim PDFPath As String
Dim App As Object
Dim AVDoc As Object
Dim pdDoc As Object
Dim jso As Object
Dim pageNo As Integer
Dim No_Of_BLs As Integer
Dim BL_Loop As Integer
Dim wb As Workbook
Dim ws As Worksheet
Dim mergedPDF As String
Dim x As Integer
'Specify the destination PDF file name
mergedPDF = ThisWorkbook.Sheets("Setup").Range("B2").Value
'Delete destination (merged) PDF if it already exists - a new PDF will be created
If Dir(mergedPDF) <> vbNullString Then Kill mergedPDF
'Specify the path of the sample PDF form.
PDFPath = ThisWorkbook.Sheets("Setup").Range("A2").Value
'Setiing Worksheet And Sheet
Set wb = ThisWorkbook
wb.Activate
Set ws = wb.Sheets("Extract PDFs")
Worksheets(ws.Name).Activate
No_Of_BLs = Cells(Rows.Count, 1).End(xlUp).Row
'Check if the file exists.
If Dir(PDFPath) = "" Then
MsgBox "Cannot find the PDF file!" & vbCrLf & "Check the PDF path and retry.", _
vbCritical, "File Path Error"
Exit Sub
End If
'Check if the input file is a PDF file.
If LCase(Right(PDFPath, 3)) <> "pdf" Then
MsgBox "The input file is not a PDF file!", vbCritical, "File Type Error"
Exit Sub
End If
On Error Resume Next
'Initialize Acrobat by creating the App object.
Set App = CreateObject("AcroExch.App")
'Check if the object was created. In case of error release the object and exit.
If Err.Number <> 0 Then
MsgBox "Could not create the Adobe Application object!", vbCritical, "Object Error"
Set App = Nothing
Exit Sub
End If
'Create the AVDoc object.
Set AVDoc = CreateObject("AcroExch.AVDoc")
'Check if the object was created. In case of error release the objects and exit.
If Err.Number <> 0 Then
MsgBox "Could not create the AVDoc object!", vbCritical, "Object Error"
Set AVDoc = Nothing
Set App = Nothing
Exit Sub
End If
On Error GoTo 0
'Open the PDF file.
If AVDoc.Open(PDFPath, "") = True Then
For BL_Loop = 2 To No_Of_BLs
'Open successful, bring the PDF document to the front.
'Use the FindText method in order to find and highlight the desired text.
'The FindText method returns true if the text was found or false if it was not.
If AVDoc.FindText(Cells(BL_Loop, 1).Value, True, True, False) = True Then
Set pdDoc = AVDoc.GetPDDoc()
Set jso = pdDoc.GetJSObject
pageNo = jso.pagenum + 1
Cells(BL_Loop, 2).Value = pageNo
End If
Next BL_Loop
Else
'Unable to open the PDF file, close the Acrobat application.
App.Exit
'Release the objects.
Set AVDoc = Nothing
Set App = Nothing
'Inform the user.
MsgBox "Could not open the PDF file!", vbCritical, "File error"
End If
End Sub
