Tutorial: how to extract data from PDF to Excel using VBA

Using the PDFTables API, you can use VBA to convert PDF to Excel.

Our algorithm can 'see' tables and understands the spacing between items to identify the rows and columns, much as your eye does when you scan a page. You can use our free API and the macro we've created to extract data from PDF to Excel using VBA, whilst in Excel.

If you haven't already, create a free account to get your API key.

The PDF Tables registration form

Create a blank workbook in Excel.

Creating a blank <a href=workbook in Excel" />

Open the Excel options window ( File → Options ) and navigate to the Customize Ribbon tab, then on the right hand side, tick the box next to Developer . Click OK to close the dialog.

Enabling the Developer <a href=menu in Excel" />

Now, open the newly added Developer tab at the end of the main ribbon, and click the Visual Basic icon (on the far left).

Opening the Visual Basic editor

Click the Insert Module button.

The Insert a VBA module button

An empty text box (the code editor) will be opened.

The VBA module code editor

Copy and paste the VBA code into the code editor.

The VBA code in the code editor

The code editor should now look something like this:

Click the Run Macro button in the toolbar, or press F5 on your keyboard, and select PDF2Workbook .

The VBA Run Macro button

When asked for your API key, enter the API key from our API page.

The API key prompt in Excel

Select the PDF file that you want to convert.

Selected PDF document in Explorer dialog

Wait for your PDF to be converted. Once it's complete, the window will refresh, and a new spreadsheet will be opened with the data from your PDF.