Uploaded June 2020 | Updated September 2026, 2 weeks ago
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
All details also available at this web-link: exceltrainingvideos.com/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
All details also available at this web-link: exceltrainingvideos.com/convert-pdf-to-excel-using-vba
