Search for a string in a PDF file from Excel

Question

I am searching for a string in a table inside a PDF file using a VBA script. The script is working when called from Word but not when called from Excel.

My PDF has many tables and the goal is to get the table number of the table containing a specific string.

Sub FindTableno()

Dim oTbl As Table
Dim oRow As Row
Dim oCell As Cell
Dim tblno As Integer

On Error Resume Next
    ' Create a "FileDialog" object as a File Picker dialog box.
    Dim fd As Office.FileDialog
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    Dim sfileName As String
    
    With fd
        .AllowMultiSelect = False
        .Filters.Clear
        .Title = "Select a PDF File"
        .Filters.Add "All PDF Documents", "*.pdf?", 1
    
        If .Show = True Then
            sfileName = Dir(.SelectedItems(1))      ' Get the file.
        End If
    End With
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    If Trim(sfileName) <> "" Then
        Dim objWord As Object       ' Create a Word object.
        Set objWord = CreateObject("Word.Application")
        objWord.Visible = False      ' Do not show the file.
        
        ' Create a Document object and open the Word file.
        Dim objDoc As Word.Document
        Set objDoc = objWord.Documents.Open(FileName:=fd.InitialFileName & sfileName, Format:="PDF Files", ConfirmConversions:=False)
    
        ' Search within tables in selected PDF file
        objDoc.Activate
    
        If ActiveDocument.Tables.Count > 0 Then
            tblno = 1
            For Each oTbl In ActiveDocument.Tables
                For Each oRow In oTbl.Rows
                    For Each oCell In oRow.Cells
                        oCell.Select
                        Selection.Find.Execute FindText:="Nutrition Information"
                        If Selection.Find.Found = True Then
                            MsgBox (tblno)
                            Exit Sub
                        Else
                        End If
                    Next
                Next
                tblno = tblno + 1
            Next
        End If
        MsgBox ("Not Found, Total Tables Searched:" & ActiveDocument.Tables.Count)
        
    End If
    Dim X As Variant
    X = Shell("powershell.exe kill -processname winword", 1)
End Sub

What error do you get. I guess none since there is an `on error` line. Remove that! — Luuklag, May 09 '19 at 10:05
Right, I do not get any errors but the output is wrong. When called from word the for loops runs till required string is found and then the variable `tblno` indicates the correct table number. But when called from excel `selection.find.found` returns positive value in the very first table it encounters instead of correctly running till the correct string is found. — Aakash Tripathi, May 09 '19 at 10:11
What line do you get this on? Did you google the error code? — Luuklag, May 09 '19 at 10:27
@Luuklag after removing `on error` I am getting Run-time Error '450':- Wrong number of arguments or invalid property assignment. What could be the reason? — Aakash Tripathi, May 09 '19 at 10:29
It just pops as a dialog box after a delay and debugger doesn't indicate where is the error line. — Aakash Tripathi, May 09 '19 at 10:35
Instead of running the code with `F5` try stepping through it row by row with `F8` to see which row throws the error. — , May 09 '19 at 10:53

Pᴇʜ · Answer 1 · 2019-05-09T11:38:59.273

0

The main issue is in this part where you use oCell.Select and afterwards Selection.Find. In this case Selection refers to the selected cell in Excel! This is because you didn't specifiy any relation to Word here, so Excel assumes you mean the selected cell in Excel.

I recommend to read How to avoid using Select in Excel VBA. The same is valid for Word VBA code.

Also don't use .Activate or you will get a similar issue. Always reference the worksheet or document directly:

If objDoc.Tables.Count > 0 Then
    tblno = 1
    For Each oTbl In objDoc.Tables
        For Each oRow In oTbl.Rows
            For Each oCell In oRow.Cells
                oCell.Range.Find.Execute FindText:="Nutrition Information"
                If oCell.Range.Find.Found = True Then
                    MsgBox (tblno)
                    Exit Sub
                End If
            Next
        Next
        tblno = tblno + 1
    Next
End If

MsgBox ("Not Found, Total Tables Searched:" & objDoc.Tables.Count)

edited May 09 '19 at 11:38

answered May 09 '19 at 11:20

Pᴇʜ

56,719
10
49
73

It did solve some of the problem for sure, but now find function is unable to find any string. When i inspected the contents of oCell.range i see a rectangle which looks like this [] instead of actual text. – Aakash Tripathi May 09 '19 at 12:16
Well I guess I cannot help further here. Actually the original issue is what I described but to find the issue you describe you will need to debug your code. Go through it step by step using F8 (and breakpoints) to analyze it and check the values of `oCell` in the `Local` window of your VBA Editor. – Pᴇʜ May 09 '19 at 12:26
thanks@Pᴇʜ ''' If objDoc.Tables.count > 0 Then tblno = 1 For Each oTbl In objDoc.Tables For Each oRow In oTbl.Rows For Each oCell In oRow.Cells pos = InStr(oCell.Range.Text, "A") If pos <> 0 Then GoTo line1 End If 'Else 'End If Next Next tblno = tblno + 1 Next End If MsgBox ("Not Found") 'MsgBox (oCell.Range.Text) End If line1: MsgBox (tblno) End Sub ''' – Aakash Tripathi May 09 '19 at 13:15
my upvotes are not getting recorded as I have a reputation less than 15. Anyways your inputs have really helped me in getting to the solution. :) – Aakash Tripathi May 09 '19 at 13:16

score 0 · Answer 2 · answered May 09 '19 at 13:21

Thanks @Pᴇʜ, this worked for me

Sub FindTableno()

Dim oTbl As Table
Dim oRow As Row
Dim oCell As Cell
Dim tblno As Integer

' Create a "FileDialog" object as a File Picker dialog box.
Dim fd As Office.FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
Dim sfileName As String

With fd
    .AllowMultiSelect = False
    .Filters.Clear
    .Title = "Select a PDF File"
    .Filters.Add "All PDF Documents", "*.pdf?", 1

    If .Show = True Then
        sfileName = Dir(.SelectedItems(1))      ' Get the file.
    End If
End With

Application.ScreenUpdating = True
Application.DisplayAlerts = True

If Trim(sfileName) <> "" Then
    Dim objWord As Object       ' Create a Word object.
    Set objWord = CreateObject("Word.Application")
    objWord.Visible = True      ' Do not show the file.

' Create a Document object and open the Word file.
Dim objDoc As Word.Document
'Set objDoc = objWord.Documents.Open(Filename:=fd.InitialFileName & sfileName, Format:="PDF Files", ConfirmConversions:=False)
 Set objDoc = objWord.Documents.Open(Filename:=fd.InitialFileName & sfileName, Format:="PDF Files", ConfirmConversions:=False)

' Search within tables in selected PDF file

If objDoc.Tables.count > 0 Then
    tblno = 1
    For Each oTbl In objDoc.Tables
        For Each oRow In oTbl.Rows
            For Each oCell In oRow.Cells
            pos = InStr(oCell.Range.Text, "Nutrition Information ")
            If pos <> 0 Then
            GoTo line1
            End If
        'Else
        'End If
        Next
        Next
        tblno = tblno + 1
        Next
    End If
    MsgBox ("Not Found, Total Tables Searched:" & objDoc.Tables.count)
    'MsgBox (oCell.Range.Text)
End If
line1:
MsgBox (tblno)
End Sub

Search for a string in a PDF file from Excel

2 Answers2