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.

1. 1
   If you haven't already, create a free account to get your API key.
   
2. 2
   Create a blank workbook in Excel.
   
3. 3
   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.
   
4. 4
   Now, open the newly added Developer tab at the end of the main ribbon, and click the Visual Basic icon (on the far left).
   
5. 5
   Click the Insert Module button.
   
6. 6
   An empty text box (the code editor) will be opened.
   
7. 7
   Copy and paste the VBA code into the code editor.
   The code editor should now look something like this:
8. 8
   Click the Run Macro button in the toolbar, or press F5 on your keyboard, and select PDF2Workbook.
   
9. 9
   When asked for your API key, enter the API key from our API page.
   
10. 10
    Select the PDF file that you want to convert.
    
11. 11
    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.
    
12. 12
    Well done, you can now extract data from PDF to Excel using VBA!