How to Extract Specific Data from PDF to Excel Using VBA

Extract Specific Data from PDF to Excel Using VBA (Code)

Sub Extract_Data_from_PDF()

Set MyWorksheet = ActiveWorkbook.Worksheets("Sheet1")
Application_Path = "C:\Program Files\Adobe\Acrobat DC\Acrobat\Acrobat.exe"
PDF_Path = "E:\ExcelDemy\standardnormaltable.pdf"

Shell_Path = Application_Path & " """ & PDF_Path & """"
Call Shell(pathname:=Shell_Path, windowstyle:=vbNormalFocus)

Application.Wait Now + TimeValue("0:00:03")

SendKeys "%vpc"
SendKeys "^a"
SendKeys "^c"

MyWorksheet.Range("A1").PasteSpecial Paste:=xlPasteAll

Call Shell("TaskKill /F /IM Acrobat.exe", vbHide)

End Sub

Extract Specific Data from PDF to Excel Using VBA: Step-by-Step Code Explanation

Consider the PDF file called standardnormaltable.pdf that contains a table of the normal distribution.

And we’ve opened a worksheet called Sheet1 in an Excel workbook where we’ll copy the data from the PDF file.

⧪ Step 1: Declaring the Necessary Inputs

These include the worksheet name, the range of the cells, the location of the application through which the PDF file will be opened (Adobe Reader in this example), and the location of the PDF file.

Set MyWorksheet = ActiveWorkbook.Worksheets("Sheet1")
Application_Path = "C:\Program Files\Adobe\Acrobat DC\Acrobat\Acrobat.exe"
PDF_Path = "E:\ExcelDemy\standardnormaltable"

⧪ Step 2: Opening the PDF File (by Using the VBA Shell Command)

Shell_Path = Application_Path & " """ & PDF_Path & """"
Call Shell(pathname:=Shell_Path, windowstyle:=vbNormalFocus)

⧪ Step 3 (Optional): Waiting for a Few Moments

This allows your computer to finish the earlier task successfully and start a new task.

Application.Wait Now + TimeValue("0:00:03")

This waits for 30 seconds. But if you want, you can wait for more.

⧪ Step 4: Using SendKeys to Copy Data from the PDF File

• ALT + V, P, C: This is mainly for enabling the scrolling in the PDF. For small files, this is not necessary. But for larger files, this becomes necessary for selecting the whole file.
• CTRL + A: This selects the whole file.
• CTRL + C: Copies the selection.

Therefore, the lines of code will be:

SendKeys "%vpc"
SendKeys "^a"
SendKeys "^c"

⧪ Step 5: Pasting the Data in the Excel File

MyWorksheet.Range("A1").PasteSpecial Paste:=xlPasteAll

This pastes into cell A1 of the worksheet and will populate the rest of the cells accordingly.

⧪ Step 6 (Optional): Closing the PDF File (Ending the Application)

Call Shell("TaskKill /F /IM Acrobat.exe", vbHide)

Read More: How to Extract Data from PDF to Excel

Example to Extract Specific Data from PDF to Excel Using VBA

We’ve seen the step-by-step procedure to extract data from a PDF file to an Excel worksheet using VBA.

Therefore, the complete VBA code to extract data from the PDF file called standardnormaltable to Sheet1 will be:

⧭ VBA Code:

Sub Extract_Data_from_PDF()

Set MyWorksheet = ActiveWorkbook.Worksheets("Sheet1")
Application_Path = "C:\Program Files\Adobe\Acrobat DC\Acrobat\Acrobat.exe"
PDF_Path = "E:\ExcelDemy\standardnormaltable.pdf"

Shell_Path = Application_Path & " """ & PDF_Path & """"
Call Shell(pathname:=Shell_Path, windowstyle:=vbNormalFocus)

Application.Wait Now + TimeValue("0:00:03")

SendKeys "%vpc"
SendKeys "^a"
SendKeys "^c"

MyWorksheet.Range("A1").PasteSpecial Paste:=xlPasteAll

Call Shell("TaskKill /F /IM Acrobat.exe", vbHide)

End Sub

⧭ Output:

Run this code. And it’ll copy data from the PDF file called “standardnormaltable” to the worksheet called “Sheet1” in the active workbook.

Read More: How to Extract Data from Multiple PDF Files to Excel

Things to Remember

• The workbook in which you’ll copy the data from the PDF file must be kept open during running the code. Otherwise, you’ll have to use the name of the workbook in the code.
• The name of the application that you are using inside the code (Adobe Acrobat DC here) must be installed on your computer. Otherwise, you’ll receive an error.
• For large data sets of PDF files, the process may take some time to copy all the data and paste. So be patient and wait until the process completes successfully.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Related Articles

• How to Export Data from Fillable PDF to Excel
• How to Link PDF Form to Excel Database
• How to Convert PDF to Excel without Software
• How to Convert PDF to Excel without Losing Formatting
• How to Convert PDF to Excel Table
• How to Export PDF Comments into an Excel Spreadsheet

<< Go Back to Import PDF to Excel | Importing Data in Excel | Learn Excel