• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help in VBA Code PDF to Excel Conversion

Hi Team,

Iam Getting Error in PDF to Excel Conversion in VBA Code,
1. After Conversion from PDF some columns are not allocating in proper
2. Some PDF not converting

Pls Help on same..

Iam getting error in
"
>>> As You Have Already Noted <<<
>>> use code - tags <<<
Code:
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 "
Code given for your reference

Code:
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")



'Dim wa As New Word.Application
wa.Visible = True
'Dim doc As Word.Document

Dim nwb As Workbook
Dim nsh As Worksheet
'Dim wr As Word.Range


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
 
Last edited by a moderator:
If you are running O365, then you can import a PDF directly without VBA.
Data-->Get & Transform Data-->Get Data-->From File-->From PDF
With your data in the Power Query Editor now, click on Close and Load. Direct where you want the converted data.
 
You can select each PDF and create a query for each. Once you have all the tables in the PQ Editor, and the layout for each is the same, you can append each to create one query and then export to Native Excel. Again, no code required. Can all be done in the UI.

 
Back
Top