pushing data from excel to word and changing the file name automatically

STUBS691

New Member
Joined
May 21, 2021
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
hi guys,

what im trying to achieve is when i run the macro in excel and it pushes the data across to my word template which works great, I want it word doc file name to change to what i have written in some cells.

below is code, thanks for any help.

Sub CopyWorksheetsToWord()
Dim wdApp As Word.Application, wdDoc As Word.Document, ws As Worksheet
Application.ScreenUpdating = True
Application.StatusBar = "Creating new document..."
Set wdApp = New Word.Application
Set wdDoc = wdApp.Documents.Open("S:\Admin\Invoice LetterHead.dotx")
For Each ws In ActiveWorkbook.Worksheets
Application.StatusBar = "Copying data from " & ws.Name & "..."
ws.UsedRange.Copy
wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range.InsertParagraphAfter
wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range.Paste
Application.CutCopyMode = False
wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range.InsertParagraphAfter
If Not ws.Name = Worksheets(Worksheets.Count).Name Then
With wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range
.InsertParagraphBefore
.Collapse Direction:=wdCollapseEnd
.InsertBreak Type:=wdPageBreak
End With
End If
Next ws
Set ws = Nothing
Application.StatusBar = "Cleaning up..."
With wdApp.ActiveWindow
If .View.SplitSpecial = wdPaneNone Then
.ActivePane.View.Type = wdPrintView
Else
.View.Type = wdPrintView
End If
End With
Set wdDoc = Nothing
wdApp.Visible = True
wdApp.Activate

Set wdApp = Nothing
Application.StatusBar = False
End Sub
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,803
Hi STUBS691. Here's an example for sheet1 A1. Remove this part of your code....
Code:
Set wdDoc = Nothing
wdApp.Visible = True
wdApp.Activate
Set wdApp = Nothing
Change the file path and/or cell to suit...
Code:
StrPath = "S:\Admin\"
Strcell = CStr(Sheets("Sheet1").Range("A" & 1).Value)
wdDoc.SaveAs Filename:=StrPath & Strcell & ".docx"
wdDoc.Close  'close doc
wdapp.Quit
Set wdDoc = Nothing
Set wdapp = Nothing
HTH. Dave
ps. please use code tags
 

STUBS691

New Member
Joined
May 21, 2021
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
Hi STUBS691. Here's an example for sheet1 A1. Remove this part of your code....
Code:
Set wdDoc = Nothing
wdApp.Visible = True
wdApp.Activate
Set wdApp = Nothing
Change the file path and/or cell to suit...
Code:
StrPath = "S:\Admin\"
Strcell = CStr(Sheets("Sheet1").Range("A" & 1).Value)
wdDoc.SaveAs Filename:=StrPath & Strcell & ".docx"
wdDoc.Close  'close doc
wdapp.Quit
Set wdDoc = Nothing
Set wdapp = Nothing
HTH. Dave
ps. please use code tags
thanks for the Advice Dave, ill remember next time for code tags.

i tried as you explained also just used bits of your code to get what i wanted which works to a point.

using your code it saved back but i was not able to open the saved file, after double clicking on it it it gives the impression word is opening up but nothing happens.

using parts of your code once word opens up when using the macro, if i use save as and override the previous save when i go to open the file it works.

any idea as to why i cannot open the previous saved file produced with vba?
 

STUBS691

New Member
Joined
May 21, 2021
Messages
7
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Thanks Dave.

VBA Code:
Sub CopyWorksheetsToWord()
Dim wdApp As Word.Application, wdDoc As Word.Document, ws As Worksheet
Dim StrPath As String
Dim Strcell As String

StrPath = "S:\Admin\Word Invoice's\"
Strcell = CStr(Sheets("Invoice").Range("C1") & (", ") & Sheets("Invoice").Range("C3") & (", ") & ("Claim") & Sheets("Invoice").Range("F5"))

Application.ScreenUpdating = True
Application.StatusBar = "Creating new document..."
Set wdApp = New Word.Application
Set wdDoc = wdApp.Documents.Open("S:\Admin\Invoice LetterHead.dotx")
For Each ws In ActiveWorkbook.Worksheets
    Application.StatusBar = "Copying data from " & ws.Name & "..."
    ws.UsedRange.Copy
    wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range.InsertParagraphAfter
    wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range.Paste
    Application.CutCopyMode = False
    wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range.InsertParagraphAfter
    wdDoc.SaveAs Filename:=StrPath & Strcell & ".docx"
  If Not ws.Name = Worksheets(Worksheets.Count).Name Then
        With wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range
            .InsertParagraphBefore
            .Collapse Direction:=wdCollapseEnd
            .InsertBreak Type:=wdPageBreak
        End With
    End If
Next ws
Set ws = Nothing
Application.StatusBar = "Cleaning up..."
With wdApp.ActiveWindow
    If .View.SplitSpecial = wdPaneNone Then
        .ActivePane.View.Type = wdPrintView
    Else
        .View.Type = wdPrintView
    End If
End With
Set wdDoc = Nothing
wdApp.Visible = True
wdApp.Activate

Set wdApp = Nothing
Application.StatusBar = False
End Sub
 

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,803
Not real sure but U shouldn't Set the wdapp = Nothing if you're going to make the doc visible. My Googling seems to indicate U should change this line of code...

Code:
Set wdDoc = wdApp.Documents.Add(Template:="S:\Admin\Invoice LetterHead.dotx", NewTemplate:=False, DocumentType:=0)
HTH. Dave
 

STUBS691

New Member
Joined
May 21, 2021
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
Not real sure but U shouldn't Set the wdapp = Nothing if you're going to make the doc visible. My Googling seems to indicate U should change this line of code...

Code:
Set wdDoc = wdApp.Documents.Add(Template:="S:\Admin\Invoice LetterHead.dotx", NewTemplate:=False, DocumentType:=0)
HTH. Dave
thanks for all the help Dave, ill have a little more of a dig I might go about it differently.
 

Forum statistics

Threads
1,141,816
Messages
5,708,747
Members
421,588
Latest member
Wawie

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top