VBA to save results to existing workbook instead of new workbook.

[Akbarov]

Hello Dear community,

I have a VBA workbook which copy data from Word document and saves it to new excel file.
Can anybody help me to save it to existing workbook instead of new workbook?

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

Thank you.

 

[Georgiboy]

Maybe:

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 twb = ThisWorkbook
    Set tsh = twb.Sheets(1)
    wr.Copy
    tsh.Paste
    doc.Close False
Next

 

[Georgiboy]

Or maybe you would want to create a sheet for each file imported?

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 twb = ThisWorkbook
    Set tsh = twb.Sheets.Add
    wr.Copy
    tsh.Paste
    doc.Close False
Next

 

[Fluff]

Can anybody help me to save it to existing workbook instead

Do you want it saved to the workbook that contains the code, or some other workbook?

 

[Akbarov]

Thank you both for fast reply. Yes I try to do it in workbook which will run VBA. I will try code and tell you if it works.

"Or maybe you would want to create a sheet for each file imported?"
Would be great..

 

[Fluff]

In that case the code from Georgiboy in post#3 should do the trick.

 

[Akbarov]

Not worked sadly.. I guess it will be better if i share full VBA , may be something missing:

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("K11").Value
excel_path = setting_sh.Range("K12").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 twb = ThisWorkbook
    Set tsh = twb.Sheets.Add
    wr.Copy
    tsh.Paste
    doc.Close False
Next

wa.Quit

MsgBox "Done"


End Sub

After Word file opened I am getting , notification message : This can take a while etc...
Is it possible to ignore that message?

 

[Georgiboy]

Hmm i have had a play with the code but not changed much, just made it easier for me to read and added a dim

Sub PDF_To_Excel()
    Dim setting_sh As Worksheet
    Dim pdf_path As String
    Dim excel_path As String
    Dim fso As New FileSystemObject
    Dim fo As Folder
    Dim f As File
    Dim wa As Object
    Dim doc As Object
    Dim wr As Object
    Dim twb As Workbook
    Dim tsh As Worksheet
   
    Set twb = ThisWorkbook
    Set setting_sh = twb.Sheets("Setting")
    pdf_path = setting_sh.Range("K11").Value
    Set fo = fso.GetFolder(pdf_path)
    excel_path = setting_sh.Range("K12").Value
    Set wa = CreateObject("word.application")
    wa.Visible = True
   
    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 tsh = twb.Sheets.Add
        wr.Copy
        tsh.Paste
        doc.Close False
    Next
    wa.Quit
    MsgBox "Done"
End Sub

As for that message box you are getting - i think the message box has a bit to tick to say don't show again.

 

[Akbarov]

I get error here : Dim fso As New FileSystemObject

 

[Georgiboy]

You will need to add a reference in the VBE for: Mirosoft Scripting Runtime