VBA Code to Convert PDF to Excel

Estimated reading: 9 minutes

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

CLICK HERE TO DOWNLOAD THE EXCEL SHEET TO CONVERT PDF TO EXCEL BY VBA

How the code works ?

Here we will divide the total program into seven section

Get the Path of the PDF Folder From a Cell of the Excel Sheet
Access to the PDF folder
Open the PDF Files in Word Application
Transfer the value of the Word object to an Excel sheet
Save the .PDF file in .XLS format
Run Loop to convert all files from PDF to Excel.
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

9 thoughts on “VBA Code to Convert PDF to Excel”

Https://Theslotsonline.Mystrikingly.Com
November 29, 2023 at 8:26 am

Thanks for one’s marvelous posting! I seriously
enjoyed reading it, you may be a great author.
I will always bookmark yourr blog and may come
back in the future. I want to encourage you to continue your greaat
writing, have a nice morning!

my blog: https://Theslotsonline.Mystrikingly.Com

https://casinovavada.blogspot.com
April 26, 2024 at 4:21 pm

I was recommended this blog by my cousin. I am
not sure wheether this post is writgen by hiim as nno one else know
such detailed about my difficulty. You’re amazing! Thanks!

Here is my blog :: https://casinovavada.blogspot.com

https://Www.Waste-Ndc.pro/community/profile/tressa79906983/
May 23, 2024 at 12:08 am

Have you eve considered writing an e-book or guest authoring
on other websites? I have a blog centered on the same ideas
you discuss and would love to have you share some stories/information.
I knokw my viewers would appreciate your
work. If you’re even remotely interested, feel free to send me an e-mail. https://Www.Waste-Ndc.pro/community/profile/tressa79906983/

Https://Casinovavada.Blogspot.Com/
May 31, 2024 at 3:49 am

When I initially commented I clicked thee “Notify me when new comments are added” checkbox
and now each time a comment is added I gget three e-mails with the same comment.
Is there any wayy you can remove people from that
service? Thanks a lot!

Take a look at my blog … https://Casinovavada.Blogspot.Com/

https://telegra.ph/Kakie-sloty-v-onlajn-kazino-Vavada-mozhno-nazvat-naibolee-populyarnymi-12-23
June 1, 2024 at 8:48 am

Howdy! This post couldn’t be written any better! Looking at this article reminds
me of my previous roommate! He constantly kept preaching
about this. I’ll send this information to him.
Pretty sure he’s going to have a great read. Thanks for sharing!

My web site: https://telegra.ph/Kakie-sloty-v-onlajn-kazino-Vavada-mozhno-nazvat-naibolee-populyarnymi-12-23

xxx
July 4, 2024 at 11:00 pm

I do agree with all the ideas you have presented in your post. They are really convincing and will certainly work. Still, the posts are too short for starters. Could you please extend them a little from next time? Thanks for the post.

roket568
July 5, 2024 at 9:09 pm

I just wanted to jot down a small comment to be able to express gratitude to you for all the pleasant ideas you are giving out on this site. My prolonged internet investigation has at the end of the day been honored with brilliant ideas to write about with my relatives. I ‘d claim that many of us readers actually are quite blessed to be in a very good network with very many wonderful people with valuable suggestions. I feel very blessed to have used your entire web site and look forward to many more pleasurable times reading here. Thank you again for everything.

PDF converter tool
August 26, 2024 at 1:25 pm

Great post. I was checking continuously this blog and I am impressed!
Extremely useful information particularly the last part :
) I care for such info a lot. I was seeking this certain information for a very long time.
Thank you and good luck.

1. Solved Excel
  October 9, 2024 at 2:40 am
  
  Thanks for giving your crucial time