Have lots of PDFs? Check out why businesses love us.

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.
    The PDF Tables registration form
  2. 2
    Create a blank workbook in Excel.
    Creating a blank workbook in Excel
  3. 3
    Open the Excel options window (FileOptions) 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 menu in Excel
  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).
    Opening the Visual Basic editor
  5. 5
    Click the Insert Module button.
    The Insert a VBA module button
  6. 6
    An empty text box (the code editor) will be opened.
    The VBA module code editor
  7. 7
    Copy and paste the VBA code into the code editor.
    The code editor should now look something like this: The VBA code in the code editor
  8. 8
    Click the Run Macro button in the toolbar, or press F5 on your keyboard, and select PDF2Workbook.
    The VBA Run Macro button
  9. 9
    When asked for your API key, enter the API key from our API page.
    The API key prompt in Excel
  10. 10
    Select the PDF file that you want to convert.
    Selected PDF document in Explorer dialog
  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.
    Screenshot of a spreadsheet that has been extracted from a PDF in Excel.
  12. 12
    Well done, you can now extract data from PDF to Excel using VBA!

PDFTables.com uses cookies to provide a service and collect information about how you use our site. If you don't want us to collect information about your site behaviour, please go to our privacy page for more information. Read about our use of cookies.