In this article, you will learn how to create a PDF to Excel converter using Excel VBA. We have created a Setting worksheet wherein we are taking 2 folders inputs on range E11 and E12 respectively.

Table of Contents

PDF to Excel Converter in Excel VBA

You need to enter the below information:

PDF Files Folder: Wherein all the PDF files are available
Excel File Folder: Wherein Excel files will be saved after conversion.

Add the Microsoft Scripting Runtime Reference in your VBA project then paste below given code in your module.

Below is the code to convert PDF to excel.

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