PDF to Excel Converter in Excel VBA: Convert a Folder of PDFs

A PDF to Excel converter in Excel VBA turns a whole folder of PDF files into Excel workbooks in one click. You enter two folder paths on a settings sheet, press Start Conversion, and the macro opens every PDF in Microsoft Word, copies its content, pastes it into a new workbook and saves it as an .xlsx file with the same name. Tables keep their rows and columns, which a plain copy and paste from a PDF reader does not do. This tutorial explains the complete macro line by line, exactly as PK shows it in the video.

Watch the Video Tutorial

In the video PK shows why copying a PDF table straight into Excel fails, runs the converter on a folder of test PDFs and then walks through the VBA code step by step.

What You Will Build

• A Setting sheet with the input folder for PDF files and the output folder for Excel files.
• A macro that loops through every file in the PDF folder with the File System Object.
• Word automation that opens each PDF as an editable Word document.
• A copy and paste step that moves the whole Word content into a new workbook and saves it as .xlsx.
• A Start Conversion button and a Done message when the folder is finished.

Why Copy and Paste from a PDF Does Not Work

PK’s test file Test-1.pdf holds an employee performance table with the columns EMP ID, EMP Name, Login hours, Call Handled, AHT and Sales. If you select that table in a PDF reader, copy it and paste it into Excel, everything lands in one column. The table structure is lost.

Word can open a PDF and rebuild it as an editable document with real tables. Once the data is in Word, copying it to Excel keeps the rows and columns. That is the whole idea behind this converter.

How the PDF to Excel Conversion Works

The macro repeats four steps for every file in the PDF folder:

1. Open the PDF file as Word.
2. Copy the Word range, meaning all of the content of that document.
3. Paste the data into a new Excel workbook.
4. Save and close the Excel file, then move to the next PDF.

Part 1 – Set Up the Setting Sheet

Step 1: Create the folders and type their paths

Create two folders, for example PDF Files with the PDFs you want to convert and an empty Excel Files folder for the results. In the workbook, rename a sheet to Setting and paste the full folder paths:

• E11: PDF files folder (input), for example C:\Users\YourName\Desktop\MyFolder\PDF Files.
• E12: Excel file folder (output), for example C:\Users\YourName\Desktop\MyFolder\Excel Files.

Save the workbook as a macro-enabled .xlsm file.

Step 2: Add the Microsoft Scripting Runtime reference

Open the Visual Basic Editor (Developer > Visual Basic or Alt+F11), choose Insert > Module, then go to Tools > References and tick Microsoft Scripting Runtime. It gives you the FileSystemObject, Folder and File types. New to it? The File System Object tutorial explains it in detail.

Part 2 – Write the PDF to Excel Macro

Step 3: Read the two folder paths

Option Explicit

Sub PDF_To_Excel()

Dim setting_sh As Worksheet
Set setting_sh = ThisWorkbook.Sheets("Setting")

Dim pdf_path As String
Dim excel_path As String

pdf_path = setting_sh.Range("E11").Value
excel_path = setting_sh.Range("E12").Value

setting_sh points to the Setting sheet, and the two string variables pick up the folder paths from E11 and E12. If your sheet or cells are different, change them here.

Step 4: Point the File System Object at the PDF folder

Dim fso As New FileSystemObject
Dim fo As Folder
Dim f As File

Set fo = fso.GetFolder(pdf_path)

fso is the File System Object, fo holds the PDF folder and f will hold each file in turn. fso.GetFolder(pdf_path) returns the folder so the loop can walk through its files.

Step 5: Start Word with late binding

Dim wa As Object
Dim doc As Object
Dim wr As Object

Set wa = CreateObject("word.application")

'Dim wa As New Word.Application
wa.Visible = True
'Dim doc As Word.Document

Dim nwb As Workbook
Dim nsh As Worksheet
'Dim wr As Word.Range

• wa is the Word application, doc the opened document and wr a Word range.
• CreateObject("word.application") starts Word without any Word reference. PK explains why: the video uses Excel 2013, which ships the Microsoft Word 15.0 Object Library. On a PC with another Office version that library has a different number and the file may throw an error. Late binding avoids that.
• The commented lines are the early-binding version (Dim wa As New Word.Application). If you prefer it, remove the apostrophes, delete the As Object lines and tick the Word Object Library in References.
• wa.Visible = True shows Word while the macro runs. nwb and nsh will hold the new workbook and its first sheet.

Step 6: Loop through the PDFs, copy and save

For Each f In fo.Files
Set doc = wa.documents.Open(f.Path, False, Format:="PDF Files")
Set wr = doc.Paragraphs(1).Range
wr.WholeStory


Set nwb = Workbooks.Add
Set nsh = nwb.Sheets(1)
wr.Copy

nsh.Paste
nwb.SaveAs (excel_path & "\" & Replace(f.Name, ".pdf", ".xlsx"))

doc.Close False
nwb.Close False
Next

wa.Quit

MsgBox "Done"

End Sub

How the code works

• For Each f In fo.Files visits every file in the PDF folder, so keep only PDFs in it.
• wa.documents.Open(f.Path, False, ...) opens the PDF in Word. f.Path returns the complete path with the file name and extension. The second argument, False, is ConfirmConversions, so Word does not ask which converter to use.
• Set wr = doc.Paragraphs(1).Range takes the range of the first paragraph, and wr.WholeStory expands that range to the entire document. Now wr holds all of the content.
• Workbooks.Add creates a new workbook, nwb.Sheets(1) is its first sheet, wr.Copy copies the Word content and nsh.Paste pastes it into the sheet.
• nwb.SaveAs saves the workbook in the Excel folder. Replace(f.Name, ".pdf", ".xlsx") keeps the file name and changes only the extension, so Test-1.pdf becomes Test-1.xlsx.
• doc.Close False and nwb.Close False close both files without saving again. After the loop, wa.Quit closes Word and the message box shows Done.

Step 7: Add the Start Conversion button and run it

Back on the Setting sheet, insert a shape from Insert > Shapes, type Start Conversion on it, right-click it, choose Assign Macro and pick PDF_To_Excel. Click the button. Word opens each PDF in turn, and when the Done message appears, the Excel Files folder holds one .xlsx file for every PDF.

One limit is shown in the video: PK’s folder also contains a scanned Form 16 PDF. A scanned file is only a picture, so the macro simply pastes that image into Excel. It cannot turn scanned pages into data.

The Complete VBA Code

Here is the full PDF to Excel converter macro from the video:

Option Explicit

Sub PDF_To_Excel()

Dim setting_sh As Worksheet
Set setting_sh = ThisWorkbook.Sheets("Setting")

Dim pdf_path As String
Dim excel_path As String

pdf_path = setting_sh.Range("E11").Value
excel_path = setting_sh.Range("E12").Value

Dim fso As New FileSystemObject
Dim fo As Folder
Dim f As File

Set fo = fso.GetFolder(pdf_path)

Dim wa As Object
Dim doc As Object
Dim wr As Object

Set wa = CreateObject("word.application")

'Dim wa As New Word.Application
wa.Visible = True
'Dim doc As Word.Document

Dim nwb As Workbook
Dim nsh As Worksheet
'Dim wr As Word.Range

For Each f In fo.Files
Set doc = wa.documents.Open(f.Path, False, Format:="PDF Files")
Set wr = doc.Paragraphs(1).Range
wr.WholeStory


Set nwb = Workbooks.Add
Set nsh = nwb.Sheets(1)
wr.Copy

nsh.Paste
nwb.SaveAs (excel_path & "\" & Replace(f.Name, ".pdf", ".xlsx"))

doc.Close False
nwb.Close False
Next

wa.Quit

MsgBox "Done"

End Sub

Make the Loop Safer (Optional Addition)

This section is an addition to the video, based on general VBA practice. The original loop sends every file in the folder to Word and its Replace is case-sensitive, so a file named REPORT.PDF would keep the wrong extension. This version skips non-PDF files, uses doc.Content for the whole document, switches off Excel’s copy mode after each paste and builds the output name safely:

Application.ScreenUpdating = False
Application.DisplayAlerts = False

For Each f In fo.Files
    If LCase(fso.GetExtensionName(f.Name)) = "pdf" Then
        Set doc = wa.Documents.Open(f.Path, False)
        Set wr = doc.Content

        Set nwb = Workbooks.Add
        Set nsh = nwb.Sheets(1)
        wr.Copy
        nsh.Paste
        Application.CutCopyMode = False

        nwb.SaveAs fso.BuildPath(excel_path, fso.GetBaseName(f.Name) & ".xlsx")
        doc.Close False
        nwb.Close False
    End If
Next

Application.DisplayAlerts = True
Application.ScreenUpdating = True

GetExtensionName and GetBaseName handle upper and lower case, BuildPath adds the backslash only when needed, and DisplayAlerts = False stops Excel asking to overwrite a file that already exists. Remember to switch alerts back on at the end.

Modern Excel Alternative (Addition)

This section is an addition to the video. In Microsoft 365 for Windows you can also import a single PDF with Power Query: Data > Get Data > From File > From PDF. Power Query lists every table and page it finds, and you load the one you need. It is great for one file with a clean table. The VBA converter is still the faster choice when you have a whole folder of PDFs to turn into separate workbooks.

Common Errors and Fixes

Compile error: User-defined type not defined

Microsoft Scripting Runtime is not ticked, so VBA does not know FileSystemObject, Folder or File. Add it in Tools > References.

Run-time error 76: Path not found

The path in E11 is wrong or the folder does not exist. Copy it from the File Explorer address bar. The output folder in E12 must exist too, otherwise SaveAs fails.

Word cannot open the PDF (Addition)

Opening PDFs as editable documents needs Word 2013 or later. The first time, Word may also show a one-time message that it will convert the PDF; tick Don’t show this message again and click OK. The PDF to Word converter macro tutorial shows this message.

Tips

• Test with two or three PDFs first, then run the full folder.
• Once the macro works, you can set wa.Visible = False so Word stays hidden while it converts.
• Text-based PDFs with simple tables give the best results; complex layouts may need a little tidying in Excel.
• Microsoft’s reference for Documents.Open lists every argument you can pass when opening a file in Word.

Want It Ready-Made?

If you want the finished tool, the PDF to Excel Converter in Excel VBA on NextGenTemplates.com comes with the Setting sheet and the Start Conversion button ready to use. Need editable documents instead of workbooks? The PDF to Word Converter Macro in Excel VBA does the same job for Word. Browse all Excel utility and converter tools for more.

Related Tutorials

• PDF to Word Converter Macro in Excel VBA
• Word to PDF Converter in Excel VBA
• Excel to PDF Converter in VBA
• File System Object: Get Files Information in Excel

Frequently Asked Questions

Can Excel VBA convert PDF to Excel without Adobe Acrobat?

Yes. The macro uses Microsoft Word to open each PDF, because Word 2013 and later can read PDF files and turn them into editable documents. Excel then copies the Word content into a new workbook. You need Excel and Word on the same PC, but no Acrobat licence, add-in or online converter.

Which reference does the PDF to Excel macro need?

Only Microsoft Scripting Runtime, for the File System Object that loops through the PDF folder. Word is started with CreateObject, which is late binding, so you do not need to tick the Microsoft Word Object Library.

Why does a simple copy and paste from a PDF not work?

When you copy a table straight from a PDF reader, Excel usually receives plain lines of text and puts everything in one column. Opening the PDF in Word first rebuilds the table structure, so the Word range pastes into Excel as rows and columns.

Can the macro convert scanned PDF files?

No. A scanned PDF is only a picture of a page, so there is no text for Word to rebuild. In the video the scanned Form 16 file was pasted into Excel as an image. You need OCR software to turn scanned pages into text first.

Where can I get the ready-made PDF to Excel converter?

The finished PDF to Excel Converter in Excel VBA, with the Setting sheet and the Start Conversion button, is available on NextGenTemplates.com.

About the Author

PK is a Microsoft Excel expert and trainer and the founder of PK: An Excel Expert and NextGenTemplates.com. He has been teaching Excel, VBA, Power Query and Power BI since 2016 on his YouTube channel. New to macros? Start with his free step-by-step VBA course.

Conclusion

A PDF to Excel converter in Excel VBA lets Word do the hard part: it opens each PDF as an editable document, and the macro copies the whole Word range into a new workbook and saves it as .xlsx. With one reference, one loop and late binding for Word, the same file converts a whole folder of text-based PDFs in one click on almost any Office PC. Build it once, keep it as an .xlsm and reuse it whenever a folder of PDF reports needs to become Excel data.