How to Extract Data from PDF to Excel Using VBA

Get FREE Advanced Excel Exercises with Solutions!

It’s a common problem that most of us face in our daily lives is how to extract some data from a PDF file to an Excel worksheet using VBA. In this article, I’ll show you how you accomplish this comfortably with proper examples and illustrations.

Table of Contents hide

Extract Data from PDF to Excel Using VBA (Quick View)

Download Practice Workbook

An Overview of the VBA Code to Extract Data from PDF to Excel (A Detailed Analysis)

Example to Extract Data from PDF to Excel Using VBA

Things to Remember

Conclusion

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"

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

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

End Sub

Download Practice Workbook

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

Extract Data from PDF.xlsm

An Overview of the VBA Code to Extract Data from PDF to Excel (A Detailed Analysis)

So, without further delay, let’s go to our main discussion today. Here we’ve got a 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.

Now I’ll show you can copy data from the PDF file to the Excel worksheet through step-by-step analysis.

⧪ Step 1: Setting the Necessary Inputs

First of all, you have to set the necessary inputs. They are 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 (Using the VBA Shell Function)

Next, we’ve to call the VBA Shell function to open the PDF file.

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

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

This is not mandatory. But when you’ve got a long series of tasks, it’s better to wait for a few moments to allow your computer to finish the earlier task successfully and start a new task.

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

Here, we’re waiting for 3 seconds. But if you want, you can wait for more.

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

This is arguably the most important task. We’ll use 3 SendKeys:

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 is for selecting the whole file.
CTRL + C: For copying the selected file.

Therefore, the lines of code will be:

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

⧪ Step 5: Pasting the Data in the Excel File

We’ve opened the specific PDF file and copied data from that file.

Now we have to paste that data into the desired range of the worksheet.

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

Here, I’ve pasted in cell A1 of the worksheet. Of course, you can paste it anywhere else according to your wish.

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

Finally, it’s better to close the running program when you are done with the data extraction.

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

Example to Extract 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"

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.

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 remain installed on your computer. Otherwise, you’ll receive an error.
For large data sets of PDF files, the process may take some time. Also sometimes you may encounter unexpected errors due to the overloading of your machine. So be patient and repeat the procedure again in case of any error.

Conclusion

Therefore, this is the process to extract some data from a PDF file to an Excel worksheet using VBA. Do you have any questions? Feel free to ask us. And don’t forget to visit our site ExcelDemy for more posts and updates.