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

[STUBS691]

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

 

[NdNoviceHlp]

Hi STUBS691. Here's an example for sheet1 A1. Remove this part of your code....

Set wdDoc = Nothing
wdApp.Visible = True
wdApp.Activate
Set wdApp = Nothing

Change the file path and/or cell to suit...

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]

Hi STUBS691. Here's an example for sheet1 A1. Remove this part of your code....

Set wdDoc = Nothing
wdApp.Visible = True
wdApp.Activate
Set wdApp = Nothing

Change the file path and/or cell to suit...

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?

 

[NdNoviceHlp]

Hmmm. Please post all the code U are using. Dave

 

[STUBS691]

Thanks Dave.

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]

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...

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

HTH. Dave

 

[STUBS691]

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...

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.