I have a lot of PDFs to convert to excel. I found the code below which converts PDF to excel by opening the pdf file in word but it fails at Dim fso As New FileSystemObject because mac excel does not have scripting dictionary. How do I amend the code to open the PDF files in word without having to use scripting dictionary?
Option Explicit
Sub pdf_to_excel()
Dim setting_sh As Worksheet
Set setting_sh = ThisWorkbook.Sheets("Setting")
Dim pdf_path As String
Dim excel_path As String
pdf_path = setting_sh.Range("E11").Value
excel_path = setting_sh.Range("E12").Value
Dim fso As New FileSystemObject
Dim fo As Folder
Dim f As File
Set fo = fso.Getfolder(pdf_path)
Dim wa As Object
Dim doc As Object
Dim wr As Object
Set wa = CreateObject("word.application")
wa.Visible = True
Dim nwb As Workbook
Dim nsh As Worksheet
For Each f In fo.Files
Set doc = wa.documents.Open(f.Path, False, Format:="PDF files")
Set wr = doc.Paragraphs(1).Range
wr.WholeStory
Set nwb = Workbooks.Add
Set nsh = nwb.Sheets(1)
wr.Copy
nsh.Paste
nwb.SaveAs (excel_path & "\" & Replace(f.Name, ".pdf", ".xlsx"))
doc.Close False
nwb.Close False
Next
wa.Quit
MsgBox "Done"
End Sub
