Convert PDF to Excel

January 12, 2023
| 26 Comments




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:UserstakyarDesktoptest1.pdf”
PathToPDF = “C:exceltrainingvideosAboutWindowsAPIs.pdf”
strCommand = “C:Program Files (x86)AdobeAcrobat Reader DCReaderAcroRd32.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:exceltrainingvideosMyPDFs”
PathToExcelFiles = “C:exceltrainingvideosPDFToExcel”
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: …(read more)

Convert Word 2 PDF: Word to PDF Converter

Convert PowerPoint 2 PDF: PPT to PDF Converter

Convert Excel 2 PDF: Excel to PDF Converter

Convert an Image 2 PDF: Image to PDF Converter

Convert HTML 2 PDF: HTML to PDF Converter

More Tools: PDF Converter

PDF Converter
| Tags: , , , , , , , , , , , , , , , , , ,