Convert PDF to Excel Using VBA

How to convert PDF files to Excel using Sendkeys and Microsoft Word with VBA.
Here’s the complete VBA code:
Option Explicit
Sub CopyPDFTextToExcel()
Dim varRetVal As Variant, PathToPDF As String, strCommand As String
Sheet1.Cells.ClearContents
Sheet1.Range(“A1”).Activate
‘PathToPDF = “C:\Users\takyar\Desktop\test1.pdf”
PathToPDF = “C:\exceltrainingvideos\AboutWindowsAPIs.pdf”
strCommand = “C:\Program Files (x86)\Adobe\Acrobat Reader DC\Reader\AcroRd32.exe ” & PathToPDF
‘ Use Shell Function to open Adobe Acrobat Reader
varRetVal = Shell(strCommand, 1)
‘ wait
Application.Wait Now + TimeValue(“00:00:05”)
‘First select all data of PDF file, next copy to clipboard
SendKeys “^a” ‘select all data
SendKeys “^c” ‘ copy data
‘Wait
Application.Wait Now + TimeValue(“00:00:05”)
‘Close Acrobat Reader or PDF file
SendKeys “%{F4}”
‘ Wait
Application.Wait Now + TimeValue(“00:00:02”)
‘ Paste to added worksheet (PDFText)
Windows(“pdf-to-excel-using-send-keys.xlsm”).Activate
ActiveSheet.Paste
End Sub

Option Explicit
Function ClearClipboard()
‘Early binding will requires a Reference to ‘Microsoft Forms 2.0 Object Library’
Dim oData As Object ‘New MSForms.DataObject
Set oData = CreateObject(“New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}”)
oData.SetText Text:=Empty
oData.PutInClipboard
Set oData = Nothing
End Function
Sub PDF_To_Excel()
Dim PathToPDFFiles As String
Dim PathToExcelFiles As String
PathToPDFFiles = “C:\exceltrainingvideos\MyPDFs\”
PathToExcelFiles = “C:\exceltrainingvideos\PDFToExcel\”
Dim fso As New FileSystemObject
Dim myFolder As Folder
Dim myFile As File
Set myFolder = fso.GetFolder(PathToPDFFiles)
Dim WordApp As Object
Dim WordDoc As Object
Dim WordRange As Object
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Set WordApp = CreateObject(“word.application”)
WordApp.Visible = True
Dim nwb As Workbook
Dim nsh As Worksheet
For Each myFile In myFolder.Files
Set WordDoc = WordApp.documents.Open(myFile.Path, False, Format:=”PDF Files”)
Set WordRange = WordDoc.Paragraphs(1).Range
WordRange.WholeStory
Set nwb = Workbooks.Add
Set nsh = nwb.Sheets(1)
WordRange.Copy
nsh.Paste
nwb.SaveAs (PathToExcelFiles & Replace(myFile.Name, “.pdf”, “.xlsx”))
Application.CutCopyMode = False
Call ClearClipboard
WordDoc.Close True
nwb.Close True
Next
WordApp.Quit
Set WordDoc = Nothing
Set WordApp = Nothing
Application.DisplayAlerts = True
Application.ScreenUpdating = True
MsgBox “Conversion complete!”
End Sub

Convert PDF to Excel with VBA
Convert PDF to Excel with VBA

Watch the video below:

Published by

Dinesh Kumar Takyar

Welcome to exceltrainingvideos.com! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: http://youtube.com/familycomputerclub For a structured Excel VBA training course online you can visit: https://www.youtube.com/excelvbaonline

2 thoughts on “Convert PDF to Excel Using VBA”

  1. Hi sir,

    I prepared a macro to copy data from pdf to excel. At run time, pdf is opening, data is copying and then pdf is closing. I want to hide all above processing. I tried with application.screenupdatin false after that problem is not resolved.

    Thanks
    Deepak

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.