Saving a Word Document with a Excel Cell Reference : VBA Excel

Question

Currently I am copying an active excel sheet and saving the data from this active excel spreadsheet to a pre-formatted word document. This pre-formatted word document then gets saved with a specific file name.

In the initial VBA setup i have declared a variable called myfilename which contains a date value. (01022019)

What I would like to learn is how I can concatenate the myfilename value with the saved filename using the SaveAs command as shown in the code. For example the saved file would be test 01022019. This is a straightforward task when saving an Excel Spreadsheet using VBA but not so when saving a word document using VBA.

I have attached some code in the hope I can get a solution:

Sub SaveAsWord()


Dim LastRow As Long
Dim objWord, objDoc As Object
Dim myfilename As String
myfilename = Sheets("Import").Range("B2")



Set wordApp = CreateObject("word.Application")
wordApp.Documents.Open "C:\test\test\worddocument.docx"
wordApp.Visible = True

With wDoc

wordApp.ActiveDocument.SaveAs Filename:="C:\test\test.docx"

End With

End Sub

Concatenation using & is the same in Word and Excel. Assuming you've tried that, are you getting an error? — BigBen
If i use wordApp.ActiveDocument.SaveAs Filename:="C:\test\test & myfilename.docx the result is test&myfilename.docx not the value in B2 — Stephen

Siddharth Rout Siddharth Rout · Accepted Answer · 2019-03-14T04:32:17

You need to use & to conctenate the path(String) and the filename (Variable) as shown below. Also you need to specify the FileFormat.

Try this

wordApp.ActiveDocument.SaveAs2 Filename:="C:\test\test\" & myfilename & ".docx", _
                               FileFormat:=wdFormatXMLDocument

or

wordApp.ActiveDocument.SaveAs2 Filename:="C:\test\test" & myfilename & ".docx", _
                               FileFormat:=wdFormatXMLDocument

Choose one from above based on the location where you want to save it.

If you are late binding then then declare this at the very top.

Const wdFormatXMLDocument as Integer = 12

Saving a Word Document with a Excel Cell Reference : VBA Excel

1 Answers