Converting PDF data into Excel

[Teenymoo]

Hello,

I'm attempting to convert PDF data into Excel and am encountering the following problem; please assist in resolving the code.

Option Explicit
Option Compare Text

Sub pdfexcelMacro()
Dim pdf_path As String
Dim excel_path As sring
Dim fileName As String
Dim xlworkbook As Workbook
Dim xlworksheet As Worksheet
Dim wordApp As New Word.Application
Dim wordDoc As Word.Document
Dim wordRange As Word.Range

'file path ***** it should be axactly same. look at slash
pdf_path = ThisWorkbook.Path & "\pdf_files\"
excel_path = ThisWorkbook.Path & "\excel_files\"
'********************************************************

Application.ScreenUpdating = False
Application.StatusBar = False

wordApp.Visible = True

fileName = VBA.Dir(pdf_path)

While fileName <> ""
If fileName Like "*.pdf" Then
Application.CutCopyMode = False

'here we open word
Set wordDoc = wordApp.Documents.Open(pdf_path & fileName, Format:="pdf files", ReadOnly:=True)
Set wordRange = wordDoc.Paragraphs(1).Range
wordRange.WholeStory

Set xlworkbook = Excel.Workbooks.Add
Set xlworksheet = xlworkbook.Sheets(1)

'here we copy the word range
wordRange.Copy

'now pasting word data in excel
xlworksheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False
xlworkbook.SaveAs (excel_path & VBA.Replace(fileName, ".pdf", "xlsx"))

xlworkbook.Close False
wordDoc.Close False
End If

fileName = VBA.Dir()
Wend

Application.CutCopyMode = False
Application.StatusBar = False
Application.ScreenUpdating = False
MsgBox "Done", vbInformation
'to display folder
Call Shell("explorer.exe" & excelpath, vbNormalFocus)

End Sub

 

[Sanjeev1976]

Dim excel_path As String

 

[alansidman]