A PDF to Excel VBA macro cannot read a PDF by itself, because VBA has no PDF parser built in. It has to hand the file to something that can: Word, which converts PDFs into editable documents, or a command-line tool that writes the text to a file. The macro then copies tables or parses lines into worksheets.
Why a VBA macro cannot read a PDF file by itself
VBA can open a PDF as raw bytes, but those bytes are compressed drawing instructions, not rows of text. Turning them into words and positions takes a full PDF parser, and neither Excel nor VBA includes one. Every working macro borrows that step from another program.
What VBA sees when it opens a PDF
A macro can read any file with Open ... For Binary. On a PDF, the result is a stream of objects: page descriptions, font programs, images, and content streams that place each run of text at a coordinate. Most content streams are compressed, so the text is not even visible in the bytes. Where it is uncompressed, it appears as short fragments interleaved with positioning operators, often encoded through the font's own character map. Rebuilding readable lines from that means decompressing, decoding fonts and tracking the drawing state, which is the work a PDF library exists to do.
Excel's own PDF import is not a VBA object
Recent versions of Excel can import a PDF through Get Data, which runs through Power Query. That import is useful, but it is driven by queries, not by a VBA method that takes a file name and returns a table. A macro can refresh a query that someone already set up, and that is a reasonable design for a monthly file with a fixed name. It cannot build the PDF import from scratch in a few lines the way it can open a CSV.
The three routes that work
Every PDF to Excel macro in practice uses one of these:
- Word as the converter. The macro starts Word, opens the PDF, which Word converts into a document, and copies the tables across.
- A command-line tool. The macro runs an external program that writes the PDF's text to a plain file, then reads that file.
- A paid PDF application with an automation interface. Some desktop PDF editors can be driven from VBA. This post does not cover them, since the details depend on the product and its licence.
The Word route: a VBA macro that opens the PDF in Word
Word can open a PDF and convert it into an editable document, rebuilding tables as Word tables. A macro in Excel can start Word in the background, open the PDF through Word's object model, loop over the document's tables, and paste each one into a new worksheet. This route needs only Office on Windows.
What Word does with a PDF
Microsoft's support page on opening PDFs in Word says Word converts the PDF into a Word document and tries to match the original layout. It says conversion works best with files that are mostly text, and it lists tables with cell spacing among the elements that often convert badly. It also warns that pages made mostly of charts or graphics may come through as a picture of the whole page. The original PDF is not changed.
The opening is done by Documents.Open. Microsoft's VBA reference for Documents.Open lists a ConfirmConversions argument, which shows the Convert File dialog when set to True for a file that is not in Word format, and a ReadOnly argument. Setting the first to False and the second to True suits a macro.
Step list
- In Excel, press Alt+F11 to open the editor, then insert a new module.
- Paste the macro below.
- Save the workbook as
.xlsm, the macro-enabled format. - Run the macro, pick a PDF in the file dialog, and wait while Word converts it.
- Check each new sheet against the PDF before using the numbers.
The macro
Sub PdfTablesViaWord()
Dim pdfPath As Variant, wd As Object, doc As Object
Dim ws As Worksheet, i As Long
pdfPath = Application.GetOpenFilename("PDF files (*.pdf), *.pdf")
If pdfPath = False Then Exit Sub
Set wd = CreateObject("Word.Application")
wd.Visible = False
wd.DisplayAlerts = 0
Set doc = wd.Documents.Open(Filename:=pdfPath, ConfirmConversions:=False, ReadOnly:=True)
For i = 1 To doc.Tables.Count
Set ws = ThisWorkbook.Worksheets.Add( _
After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
ws.Name = "Table_" & i
doc.Tables(i).Range.Copy
ws.Activate
ws.Range("A1").Select
ws.Paste
Next i
doc.Close SaveChanges:=False
wd.Quit
Set doc = Nothing: Set wd = Nothing
End Sub
CreateObject uses late binding, so the macro runs without adding a reference to the Word library. The value 0 for DisplayAlerts asks Word not to show its prompts; depending on your Office version, a one-time conversion notice may still appear the first time.
What to expect from the output
Word's conversion is a best guess at the layout, so treat each sheet as a draft. Common results:
- A table Word did not recognise arrives as tab-separated paragraphs, not as a table, and is skipped by the loop.
- A table split across two pages arrives as two Word tables, and so as two sheets.
- Merged header cells come across merged, which then blocks sorting until you unmerge them.
When the sheets need repairs, the steps for checking a converted PDF table column by column apply to macro output as much as to any converter.
Error handling
If the macro stops halfway, Word can be left running invisibly, holding the PDF open. Add an On Error GoTo block that closes the document and quits Word before exiting. Check Task Manager the first few times you test, and end any stray Word process before running again.
Calling a command-line PDF tool from a VBA macro
A VBA macro can run a command-line PDF tool with the Windows Script Host, wait for it to finish, and read the text file it writes. Free tools exist that dump a PDF's text with its layout kept, using runs of spaces to hold columns in place, so the macro can then split each line.
Running the tool and waiting
The macro needs to wait until the text file exists. The Shell function in VBA starts a program and returns at once, which leaves the macro reading a file that is not written yet. The Run method of the WScript.Shell object takes a wait flag, which avoids the race:
Sub PdfTextViaCommandLine()
Dim sh As Object, cmd As String, txtPath As String
Dim pdfPath As String: pdfPath = "C:\Reports\supplier-prices.pdf"
txtPath = Environ$("TEMP") & "\pdf-text.txt"
cmd = "pdftotext -layout """ & pdfPath & """ """ & txtPath & """"
Set sh = CreateObject("WScript.Shell")
If sh.Run(cmd, 0, True) <> 0 Then
MsgBox "The PDF tool reported an error."
Exit Sub
End If
Workbooks.OpenText Filename:=txtPath, DataType:=xlDelimited, Tab:=False, Other:=False
End Sub
The example calls a tool named pdftotext, which is one common choice; the program must be installed and on the system path, or the command must give its full location. The second argument to Run hides the window, and the third makes VBA wait.
Reading the text back
Workbooks.OpenText opens the file with one line per row, all in column A. From there, the macro, or a formula, splits each line. Splitting fixed-layout text on runs of two or more spaces keeps descriptions with single spaces intact. The guide to extracting a table with a command-line tool compares the layout options these tools offer and what each one does to columns.
Quoting paths
Paths with spaces break the command unless they are wrapped in double quotes, which in VBA means doubling the quote characters inside the string, as in the example. Print cmd with Debug.Print before running it the first time and check the result in the Immediate window.
What can a VBA macro do with PDF text once it is in Excel?
Once the PDF's text is in a worksheet, a VBA macro is good at the repetitive clean-up: splitting lines into columns, dropping page headers and footers, joining wrapped rows, converting text amounts to numbers, and moving the result into a fixed layout. Those are the jobs that take most of the time by hand.
Jobs that suit a macro
- Deleting junk rows. Loop from the bottom up and delete rows that match a page header, a footer or a blank line. Bottom up matters, because deleting while moving down skips the row after each deletion.
- Merging continuation lines. A row with text but no date and no amount belongs to the row above. Append its text and delete it.
- Fixing numbers. Strip thousands separators, turn bracketed amounts into negatives, and write the result as a real number with
CDbl. - Keeping only some columns. When you need just the dates or just the amounts, a macro that copies one column after the split is quick to write; pulling a single column out of a PDF table covers the non-macro routes too.
Regular expressions in VBA
VBA can use the VBScript regular expression object through CreateObject("VBScript.RegExp"), which helps when lines follow a pattern, such as a date at the start and two amounts at the end. One pattern with groups can pull all three pieces out of a line in a single match. Test the pattern on a dozen real lines before running it over a whole file.
Keep the raw text
Write cleaned data to a new sheet and leave the raw lines where they landed. When a value looks wrong later, the raw sheet shows whether the PDF tool, the macro or the source document caused it.
Does a PDF to Excel VBA macro work on a Mac or with scanned files?
A PDF to Excel VBA macro that uses the Windows Script Host does not work on a Mac, and one built on Word automation needs testing there before you trust it. It also does nothing useful with scanned PDFs, because a scan holds only an image of the text.
Mac limits
Excel for Mac runs VBA, but the WScript.Shell object belongs to Windows, so the command-line route fails there outright. Whether the Word route works depends on your Office version and setup, so test it on a Mac before relying on it. If the people who run your macro use Macs, plan for that from the start, or choose a method that does not depend on VBA.
Scanned and image PDFs
Open the PDF and try to select a word. If nothing highlights, the page is an image. Word may still open it, but the result will be a picture or garbled text, and the text tool will write an empty file. Text recognition has to run first, either in a PDF editor or a separate tool, before any macro has something to read.
Protected PDFs
A PDF with an open password stops both routes unless the macro supplies the password. A PDF with copy restrictions may convert in one tool and not another. Do not build a macro around getting past restrictions on files you were not given rights to.
When a VBA macro is the wrong tool for PDF to Excel
A VBA macro is the wrong tool when the PDF layout changes from file to file, when the file is a scan, or when the people running it are not on Windows. It is also the wrong tool for a one-off job, where setting up and testing the macro takes longer than converting the file by hand.
Signs a macro will cost more than it saves
- Each file comes from a different source with a different layout, so the parsing code needs changing every time.
- The macro has to be shared with people who cannot or will not enable macros, which some organisations block outright.
- You need the output in another program, not in Excel.
Better fits
For a single table, a converter or Excel's PDF import is quicker. For repeated work on many files, a script in a language with a real PDF library, run outside Excel, is easier to test and maintain. For people who work in the browser all day, what a PDF to Excel browser extension can and cannot do is worth reading before writing any code.
For bank statements specifically, FastBankConvert is being built to read the statement layout itself and return Excel or CSV, with a typical digital statement meant to take seconds and no macro to maintain. It has not opened yet, and the get-started page takes your email for launch.