PDA

View Full Version : Sleeper: VBA Saving a Word Document with a reference to an Excel Cell



ireland87
07-31-2015, 10:05 AM
Hi all

can anyone help i am trying to have a unique filename for the Word documents that this VBA produces. It needs to reference a cell in the excel sheet. The cell im trying to reference is call sysnm which will be unique each time this code is run in a bigger loop.



Sub CreateBasicReport()
Dim wdApp As Word.Application
Set wdApp = New Word.Application
With wdApp
'wont see word appear
'.Visible = True
'.Activate
'specifying a template file to work from
.Documents.Add "C:\Users\shaun media\Documents\Custom Office Templates\SystemTemplate1.dotx"
Range("B1", Range("B1").End(xlDown).End(xlToRight)).Copy
'pasting to the bookmark 1
.Selection.GoTo What:=-1, Name:="MonthData"
.Selection.Paste
'system name (not working to rename)
sysnm = Sheets("Master").Range("B11").Value
.ActiveDocument.SaveAs2 Environ("UserProfile") & "\Desktop\System" & "sysnm" & ".docx"
.ActiveDocument.Close
.Quit
End With

excelliot
08-01-2015, 01:11 AM
you can get help from here : http://www.thespreadsheetguru.com/blog/2014/8/19/save-as-a-new-version-if-file-already-exists

do let us know if you need further help..