Macro to save file using table cells in the filename

[Lorna Bailey]

Hi.

I am new to VBA and to date have managed to work out most things through internet searches, but I am stuck on my latest one.

I have a simple word (2010) template that contains a simple two column table (attached).

I then want to create a macro that once run saves the document with a filename created from the table contents (column 2, rows 3, 1 and 2).

I have got this far:

Sub Save()
'
' Save Macro
'
'
Dim strCellText As String
strCellText = ActiveDocument.Tables(1).Rows(3).Cells(2).Range.text & " - " & ActiveDocument.Tables(1).Rows(1).Cells(2).Range.text
Debug.Print strCellText
ChangeFileOpenDirectory _
"S:\Human Resources\HR - Payroll Project Team\Transports\Documentation on Transports completed\"
ActiveDocument.SaveAs2 FileName:= _
"S:\Human Resources\HR - Payroll Project Team\Transports\Documentation on Transports completed\" _
& strCellText & ".docm" _
, FileFormat:=wdFormatXMLDocumentMacroEnabled, LockComments:=False, _
Password:="", AddToRecentFiles:=True, WritePassword:="", _
ReadOnlyRecommended:=False, EmbedTrueTypeFonts:=False, _
SaveNativePictureFormat:=False, SaveFormsData:=False, SaveAsAOCELetter:= _
False, CompatibilityMode:=14
End Sub

However, it keeps failing with the message also in the attachment.

I was also having issues when I added on the date field (strCellText = ActiveDocument.Tables(1).Rows(3).Cells(2).Range.text & " - " & ActiveDocument.Tables(1).Rows(1).Cells(2).Range.text & " - " & Format(ActiveDocument.Tables(1).Rows(2).Cells(2).Range.text, "yyyymmdd")) as it didn't seem to be reformatting the date, so I must have something wrong here too.

Any help is greatly appreciated!

Lorna