Opening pdf files through MS-Word

Question

I am trying to open a pdf file through MS Word, perform certain action such as evaluating calculations, printing the files, etc. and then proceed with closing the file. The error message I received is "Microsoft Excel is waiting for another application to complete an OLE action."

I have previously tried hyperlinkfollow and Shell MyPath & " " & MyFile, vbNormalFocus method, it doesn't work. I am still at the starting phase of opening the pdf files, please advice. Thanks!

Sub Extract_PDF_Data()

Dim mainData As String
Dim strFile As String
Dim Oldname As String
Dim Newname As String
Dim Folderpath As String

Dim s As String
Dim t As Excel.Range
Dim wd As New Word.Application
Dim mydoc As Word.Document



Folderpath = InputBox("Folder path: ")
Folderpath = Folderpath & "\"
strFile = Dir(Folderpath & "", vbNormal)


Do While Len(strFile) > 0
Oldname = Folderpath & strFile
Set wd = CreateObject("Word.Application")
Set mydoc = Word.Documents.Open(Filename:=Oldname, Format:="PDF Files", 
ConfirmConversions:=False)

mainData = mydoc.Content.Text
mydoc.Close False
wd.Quit


strFile = Dir
Loop

End Sub

Answer 1

Don't us the New keyword in the line that declares the object variable. This will "block" the object variable - it causes the error when the code laters tries to instantiate it. This method can work in VB.NET but not in VBA.

Do it more like this:

Dim wd As Word.Application
Set wd = New Word.Application. 'Or use CreateObject

Answer 2

I think a combination of those three sources will lead to the answer:

How to open a pdf with Excel?

How to extract data from pdf using VBA?

How to open and print a pdf using VBA?

I think it will be something like this:

Sub Extract_PDF_Data()

Dim mainData As String
Dim strFile As String
Dim Oldname As String
Dim Newname As String
Dim Folderpath As String
Dim s As String
Dim t As Excel.Range
Dim Appshell As Variant
Dim ap As String
Dim Browsedir As Variant
Dim f As Variant
Dim KeyWord As String

' This is a suggestion, I use it because it is more convenient than copy-pasting folder paths
Dim FSO As Object
Set FSO = CreateObject("Scripting.Filesystemobject")

 ' Get Folder over user input
Set Appshell = CreateObject("Shell.Application")
Set Browsedir = Appshell.BrowseForFolder(0, "Select a Folder", &H1000, "E:\Xample\Path")

' check if not cancalled
If Not Browsedir Is Nothing Then
      Folderpath = Browsedir.items().Item().Path
Else
    GoTo Quit
End If

KeyWord = "The_Materialist_Example"

' go through all files in the folder
For Each f In FSO.GetFolder(Folderpath).Files
    ' if file is a pdf , open, check for keyword, decide if should be printed
    If LCase(Right(f.Name, 3)) = "pdf" Then

        ' Here the methods suggest different answers.
        ' You can either use FollowHyperLink or use the Adobe Library to OPEN PDF

        ' I would write a function that checks the active pdf for the keyword : IsKeyFound
        Debug.Print Folderpath & "\" & f.Name

        Call PrintPDF(Folderpath & "\" & f.Name)
        If IsKeyFound(f, KeyWord) Then
            f.Print
        End If
    End If
Next f

Quit:
End Sub

Private Sub PrintPDF(strPDFFileName As String)
  Dim sAdobeReader As String 'This is the full path to the Adobe Reader or Acrobat application on your computer
  Dim RetVal As Variant
  sAdobeReader = "C:\Program Files (x86)\Adobe\Acrobat Reader DC\Reader\AcroRd32.exe"
  'Debug.Print sAdobeReader & "/P" & Chr(34) & strPDFFileName & Chr(34)
  RetVal = Shell(sAdobeReader & " /P " & Chr(34) & strPDFFileName & Chr(34), 0)
End Sub


Private Function IsKeyFound(PDF As Variant, KeyWord As String) As Boolean
   'Decide if file needs to be printed, insert your criteria and search algorithm here
End Function

I have not been able to figure out how to extract the keywords, you could however use a user input as a first approach and later move on to a automated scan of the pdf.

I hope this gets you further on the way to the solution.