title: Looping Through PDF Files
body: <p>I have a working VBA script that pulls specific form fields from a specified PDF file into a spreadsheet. However I have several hundred PDFs that I need to do this for, so I'd like to loop through files in a directory and perform the same action.</p>
  
  <p>Conveniently I have an old VBA script that loops through Word files in a directory and imports the content of each just how I'd like.</p>
  
  <p>I hardly know VBA but I've adapted scripts in several language including VBA to meet my needs. I thought this would take 10 minutes but its taken several hours. Can somebody please look at my script below and tell me where I'm going wrong? I assume it has something to do with the Word and Acrobat libraries having different requirements, but even my loop isn't displaying the test message.</p>
  
  <p>PS I have Acrobat Pro installed.</p>
  
  <p><strong>My Script (Non-Working)</strong></p>
  
  <pre><code>Private Sub CommandButton1_Click()
      Dim f As String: f = "C:\temp\ocopy"
      Dim s As String: s = Dir(f &amp; "*.pdf")
      Dim AcroApp As Acrobat.CAcroApp
      Dim theForm As Acrobat.CAcroPDDoc
      Dim jso As Object
      Dim text1, text2 As String
      Dim col As Integer: col = 1
  
      Do Until s = ""
          Set AcroApp = CreateObject("AcroExch.App")
          Set theForm = CreateObject("AcroExch.PDDoc")
  
          theForm.Open (f &amp; s)
          Set jso = theForm.GetJSObject
  
          text1 = jso.getField("Name of serviceRow1").Value
          text2 = jso.getField("Who are the key contacts?").Value
          MsgBox text1
          MsgBox "text1"
          Sheet1.Cells(col, 1).Value = text1
          Sheet1.Cells(col, 2).Value = text2
          col = col + 1: s = Dir
          theForm.Close
  
          AcroApp.Exit
          Set AcroApp = Nothing
          Set theForm = Nothing
      Loop
  End Sub
  </code></pre>
  
  <p><strong>Word Script - Works at Looping and Importing</strong></p>
  
  <pre><code>Sub fromWordDocsToMultiCols()
      Dim f As String: f = "C:\temp\Test\"
      Dim s As String: s = Dir(f &amp; "*.docx")
      Dim wdApp As New Word.Application, wdDoc As Word.Document
      Dim col As Integer: col = 1
  
      On Error GoTo errHandler
      Do Until s = ""
          Set wdDoc = wdApp.Documents.Open(f &amp; s)
          wdDoc.Range.Copy
          Sheet1.Cells(1, col).Value = s
          Sheet1.Cells(2, col).PasteSpecial xlPasteValues
          wdDoc.Close False: col = col + 1: s = Dir
      Loop
  
  errHandler:
      If Err.Number &lt;&gt; 0 Then MsgBox Err.Description
      If Not wdApp Is Nothing Then wdApp.Quit False
  End Sub
  </code></pre>
  
  <p><strong>Acrobat Script - Works as Importing One-by-One</strong></p>
  
  <pre><code>Private Sub CommandButton1_Click()        
      Dim AcroApp As Acrobat.CAcroApp
      Dim theForm As Acrobat.CAcroPDDoc
      Dim jso As Object
      Dim text1, text2 As String
  
      Set AcroApp = CreateObject("AcroExch.App")
      Set theForm = CreateObject("AcroExch.PDDoc")
      theForm.Open ("C:\temp\ocopy\Minerals asset management.pdf")
      Set jso = theForm.GetJSObject
  
      ' get the information from the form fiels Text1 and Text2
      text1 = jso.getField("Name of serviceRow1").Value
      text2 = jso.getField("Who are the key contacts within the team for this service? Please provide one contact per region").Value
  
  
  
      Sheet1.Cells(1, 1).Value = text1
      Sheet1.Cells(1, 2).Value = text2
      theForm.Close
  
      AcroApp.Exit
      Set AcroApp = Nothing
      Set theForm = Nothing
  
  
  End Sub
  </code></pre>
  
  <p>Many thanks in advance.</p>