Automate

[MS7]

Hey all,

I am new to VBA and I would like to automate the spreading exercise which is basically taking data from PDF financial statements and moving them to excel. Is there anyway to automate this task ?

 

[Logit]

Internxt Drive – Private & Secure Cloud Storage

 

[NdNoviceHlp]

@Logit I don't understand your reply and the link lead to a site that's been reported as unsafe? Dave

 

[Logit]

That website is safe. It is a 'cloud' site for storing and downloading files. I have a large number of files stored there. Sorry for the confusion. THe file is a
conversion file PDF to Excel.

 

[NdNoviceHlp]

@Logit Thanks for the clarification and re-assurance as I am interested in the subject matter. Hopefully *MS7 will also benefit. Have a nice day. Dave

 

[Fluff]

@Logit you should now by know what the rules are. Please post your solution to the site & not just a link to a file.

 

[Logit]

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