title: How do I copy and paste data from pdf to excel?
body: <p>Scenario: I have tons of Invoices received in my e-mail and saving them in a <code>folder("C:\Users\Vbattul2\Desktop\Invoices\")</code> and manually opening them one by one copying the needed data and pasting them to my excel tracker. Note that all the invoices have the same formats.</p>
  
  <p>Opportunity to Automate: Automate the process of copy and paste of needed data from pdf and paste it to excel dummy worksheet(data delimited with colon &amp; blank) and paste cell references in Excel invoice tracker.</p>
  
  <p>Design: I will need to copy the pdf and paste it to a dummy worksheet then paste it to my excel invoice tracker.</p>
  
  <p>I tried running this code from a thread posted 3 years ago:</p>
  
  <p><a href="https://stackoverflow.com/questions/28835164/vba-copying-data-from-pdf/37287583#37287583?newreg=17cbda959f594ea188ed3d98ae790c9c">VBA Copying data from pdf</a></p>
  
  <p>I couldn't seem to understand this line, so it is returning me an </p>
  
  <pre><code>error: 1004 Method 'Range' of object'_Global failed
  
  Line Error: For Each fName In Range("path")
  </code></pre>
  
  <p>I also tried replacing the ("path") to my folder path where I save all the invoices but it doesn't work.</p>
  
  <pre><code>Sub StartAdobe1()
      Dim fName       As Variant
      Dim wbTransfer  As Excel.Workbook
      Dim wsNew       As Excel.Worksheet
      Dim dOpenCol    As Double
      Dim oPDFApp     As AcroApp
      Dim oAVDoc      As AcroAVDoc
      Dim oPDDoc      As AcroPDDoc
  'Define your spreadsheet
  Set wbTransfer = Workbooks("transfer.xlsm")
  Set wsNew = wbTransfer.Sheets("new")
  'Find first open column
  dOpenCol = wsNew.Cells(1, Columns.Count).End(xlToLeft).Column + 1
  
  'Instantiate Acrobat Objects
  Set oPDFApp = CreateObject("AcroExch.App")
  Set oAVDoc = CreateObject("AcroExch.AVDoc")
  Set oPDDoc = CreateObject("AcroExch.PDDoc")
  
  For Each fName In Range("path")
  
  'Open the PDF file. The AcroAVDoc.Open function returns a true/false
  'to tell you if it worked
  If oAVDoc.Open(fName.Text, "") = True Then
      Set oPDDoc = oAVDoc.GetPDDoc
  Else
      Debug.Assert False
  End If
  
  'Copy all using Acrobat menu
  oPDFApp.MenuItemExecute ("SelectAll")
  oPDFApp.MenuItemExecute ("Copy")
  
  'Paste into open column
  wbTransfer.Activate
  wsNew.Cells(1, dOpenCol).Select
  ActiveSheet.Paste
  
  'Select next open column
  dOpenCol = dOpenCol + 1
  
  oAVDoc.Close (1)    '(1)=Do not save changes
  oPDDoc.Close
  
  Next
  'Clean up
  Set wbTransfer = Nothing
  Set wsNew = Nothing
  Set oPDFApp = Nothing
  Set oAVDoc = Nothing
  Set oPDDoc = Nothing
  
  
  End Sub
  </code></pre>
  
  <p>i have uploaded an attachment where the actual result in dummy worksheet(paste as delimited values - colon &amp; blank) should look like and the excel invoice tracker.</p>
  
  <p>here are the links.</p>
  
  <p><a href="https://www.dropbox.com/preview/Marketing/desired%20output%20from%20pdf%20to%20excel.xlsm?role=work" rel="nofollow noreferrer">https://www.dropbox.com/preview/Marketing/desired%20output%20from%20pdf%20to%20excel.xlsm?role=work</a></p>
  
  <p><a href="https://www.dropbox.com/preview/Marketing/MRC%201380%20INVENTORY%202019.xlsb?role=work" rel="nofollow noreferrer">https://www.dropbox.com/preview/Marketing/MRC%201380%20INVENTORY%202019.xlsb?role=work</a></p>