VBA Code to Convert PDF to Excel

This tutorial will teach us to convert PDF files to Excel files with simple VBA code.

Option Explicit 'Purpose: Converting files from pdf to Excel format(.xlsx) Sub PDF_TO_EXCEL_CONVERT() 'Define the veriable for the pdf folder path Dim pdf_folder As String 'Veriable to get access to the computer's file system Dim sFSO As New FileSystemObject Dim sfolder As folder Dim sfile As File 'Veriable to create a new ActiveX object in the word application Dim word As Object Dim document As Object Dim word_range As Object 'Veriable to create a new excel object Dim excel_workbook As Workbook Dim excel_worksheet As Worksheet 'Assign the value of cell D8 to the pdf_folder variable pdf_folder = ThisWorkbook.Sheets("Sheet1").Range("D8").Value 'Access to the path of the pdf_folder and assaign to the sfolder variable Set sfolder = sFSO.GetFolder(pdf_folder) 'Runs word application Set word = CreateObject("word.application") word.Visible = True 'Veriable to count the files in pdf folder Dim i As Integer i = 0 'loop to run the enclosed code for every file in the pdf_folder For Each sfile In sfolder.Files 'opens the files in the pdf_folder and assign to the document variable Set document = word.Documents.Open(sfile.Path, False, Format:="PDF Files") 'select and pass the values in the document variable to the word_range variable Set word_range = document.Paragraphs(1).Range 'select the whole values assigned to the word_range  word_range.WholeStory 'copies the values of the word_range variable to the clipboard   word_range.Copy 'adds new workbook and assign to the excel_workbook variable Set excel_workbook = Workbooks.Add 'adds new worksheet and assign to the excel_worksheet variable Set excel_worksheet = excel_workbook.Sheets(1) 'paste the values of the word_range variable from clipboard to the excel_worksheet variable  excel_worksheet.Paste 'save the excel_workbook object in the same directory of pdf file and repalce the file extension to .xlsx  excel_workbook.SaveAs Filename:=pdf_folder & Replace(sfile.Name, ".pdf", ".xlsx")  document.Close False  excel_workbook.Close False i = i + 1 Next word.Quit 'pass the value of i (after running the loop) and the text in the cell D13 Range("D13").Value = i & " -files have been converted into Excel" End Sub '____________Additonal code to make the code more dynamic_______________ 'Purpose: To open folder picker window and get the folder path Sub GET_PDF_FOLDER() 'Veriable to get access to the computer's file system Dim get_folder As FileDialog Dim pdf_folder As String 'creates a folder browser window Set get_folder = Application.FileDialog(msoFileDialogFolderPicker) With get_folder 'to give a title to file browser window  .Title = "Select the pdf_folder" 'disable the user to multiselect  .AllowMultiSelect = False 'make visibe the folder browser window and determines if the user chose OK or Cancel. If .Show <> -1 Then Exit Sub 'get the path of seclected folder and assign to pdf_folder variable pdf_folder = .SelectedItems(1) & "\" End With 'pass the value of pdf_folder to the cell D8 Range("D8").Value = pdf_folder End Sub

Note: Please check the references before running the code in Excel

vba code to convert pdf to excel

How the code works ?

Here we will divide the total program into seven section

  1. Get the Path of the PDF Folder From a Cell of the Excel Sheet
  2. Access to the PDF folder
  3. Open the PDF Files in Word Application
  4. Transfer the value of the Word object to an Excel sheet
  5. Save the .PDF file in .XLS format
  6. Run Loop to convert all files from PDF to Excel.
  7. Additional Section to Make the Code More Dynamic

Section A. Get the Path of the PDF Folder From a Cell of the Excel Sheet

Suppose you inserted the path of the PDF folder in cell D8 in Sheet 1. The following lines of code will assign the values from cell D8 to the variable pdf_folder

'Define the variables for the pdf folder path Dim pdf_folder As String 'Assign the value of cell D8 to the pdf_folder variable pdf_folder = ThisWorkbook.Sheets("Sheet1").Range("D8").Value

Section B. Access to the PDF folder

We will use the FileSystemObject method to access the PDF folder. First, we create a variable (sFSO) as New FileSystemObject. Then assign the folder from the path of the PDF folder to sfolder variable by sFSO.GetFolder(pdf_folder) method

'Define the variable to get access to the computer's file system Dim sFSO As New FileSystemObject Dim sfolder as Folder 'Access to the path of the pdf_folder and assign to the sfolder variable Set sfolder = sFSO.GetFolder(pdf_folder)

Section C. Open the PDF Files in Word Application

Part 1: Crete a object in word application

We will use the CreateObject(“word.application”) method to create an object and assign the object to the word variable. Then, to make the Word application visible on the screen, set the word.Visible as TRUE

'Veriable to create a new ActiveX object in the Word application Dim word As Object 'Runs word application Set word = CreateObject("word.application") word.Visible = True

Part 2: Code to open PDF files in the Word application

The following line of code will open the file in the PDF folder and assign it to the document variable.

Dim document As Object 'opens the files in the pdf_folder an creates a document object Set document = word.Documents.Open(sfile.Path, False, Format:="PDF Files")

Section D: Transfer the value of the Word object to an Excel sheet

Part 1: Copy the values from the word object

The document.Paragraphs(1).Range property selects the range of the value of the document variable. Then assign to the variable named word_range.

Then word_range.Copy method copies the values to the clipboard of the computer.

Dim word_range As Object 'select the values in the document object and pass the values to the word_range variable Set word_range = document.Paragraphs(1).Range 'select the whole values assigned to the word_range word_range.WholeStory 'coppies the values from the word_range variable and paste the values to the excel_worksheet variable word_range.Copy

Part 2: Create an Excel sheet

The method Workbooks.Add create a Excel workbook and assign it to the variable excel_workbook. Similarly, excel_workbook.Sheets(1) method will add a sheet to the Excel workbook we created.

'Veriable to create a new excel file object Dim excel_workbook As Workbook Dim excel_worksheet As Worksheet 'adds new workbook and assign to the excel_workbook variable Set excel_workbook = Workbooks.Add 'adds new worksheet and assign to the excel_worksheet variable Set excel_worksheet = excel_workbook.Sheets(1)

Part 3: paste the values of the word_range variable from the clipboard to Sheet 1

The following line of code will paste the values from the computer’s clipboard to the excel_worksheet variable.

excel_worksheet.Paste

Section E. Save the .PDF file in .XLSX format

We used the SaveAs method to save the workbook. The replace function was used to replace the file extension from .pdf to .xlsx

excel_workbook.SaveAs Filename:=pdf_folder & Replace(sfile.Name, ".pdf", ".xlsx")

Section F. Run the loop to convert all files from PDF to Excel.

The following loop will run the enclosed code for every file in the PDF folder.

'loop to run the code for every file in the pdf_folder For Each sfile In sfolder.Files 'opens the files in the pdf_folder an creates a document object Set document = word.Documents.Open(sfile.Path, False, Format:="PDF Files") 'pass the values in the document object to the word_range variable Set word_range = document.Paragraphs(1).Range 'select the whole values assigned to the word_range word_range.WholeStory 'coppies the values from the word_range variable and paste the values to the excel_worksheet variable word_range.Copy 'adds new workbook and assign to the excel_workbook variable Set excel_workbook = Workbooks.Add 'adds new worksheet and assign to the excel_worksheet variable Set excel_worksheet = excel_workbook.Sheets(1) excel_worksheet.Paste 'save the excel_workbook object in the same directory of the pdf file and replace the file extension to .xlsx excel_workbook.SaveAs Filename:=pdf_folder & Replace(sfile.Name, ".pdf", ".xlsx") document.Close False excel_workbook.Close False Next

Section G. Additional Section to Make the Code More Dynamic

Part 1: Create a VBA code to show the count of converted files in the cell D13

The following code will add 1 to the value of i for each file in the PDF folder.

And finally, the value of i will be passed to the cell D13 by the Range(“D13”).Value method.

'Veriable to count the files in pdf folder Dim i As Integer i = 0 For Each sfile In sfolder.Files i = i + 1 Next 'pass the value of i (after running the loop) and the text in the cell D13 Range("D13").Value = i & " -files have been converted into Excel"

Part 2: Create an automated folder picker by VBA

If you want to get rid of copying the path of the PDF folder every time, you can apply the following code to create an automated folder path picker

'Purpose: To open folder picker window and get the folder path Sub GET_PDF_FOLDER() 'Veriable to get access to the computer's file system Dim get_folder As FileDialog Dim pdf_folder As String 'creates a folder browser window Set get_folder = Application.FileDialog(msoFileDialogFolderPicker) With get_folder 'to give a title to browser window  .Title = "Select the pdf_folder" 'disable the user to multiselect  .AllowMultiSelect = False 'make visibe the folder browser window and determines if the user chose OK or Cancel. If .Show <> -1 Then Exit Sub 'get the path of seclected folder and assign to pdf_folder variable pdf_folder = .SelectedItems(1) & "\" End With 'pass the value of pdf_folder to the cell D8 Range("D8").Value = pdf_folder End Sub