VBA macro to save excel file using path from cell

Question

I'm trying to save out a file using a combination of hard line and cell value to determine the file path.

In cell A29, I have a formula that outputs this:

2014\January\High Cash 1.7.14

I'm getting an Expected: end of statement error.

The code is:

ActiveWorkbook.SaveAs Filename:="S:\IRD\Tamarac\Daily High Cash Reporting\& Range("A29").Text & ".xlsx", FileFormat:= _  xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False

Answer 1

Suggest you go a step further and ensure any invalid file name characters that will case a save error are filtered out

This code removes

[]/:*?"<>

main code

Sub CleanSave()
Dim fileName As String
fileName = "C:\temp\" & strClean(Range("A29").Value) & ".xlsx"
ActiveWorkbook.SaveAs fileName:=fileName, FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
End Sub

cleaning function

Function strClean(strIn As String) As String
Dim objRegex As Object
Set objRegex = CreateObject("vbscript.regexp")
With objRegex
    .Pattern = "[\[\]|\/\\:\*\?""<>]"
    .Global = True
    strClean = .Replace(strIn, vbNullString)
End With
End Function