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

Syntax highlighting of SO already shows the mistake.

sancho.s ReinstateMonicaCellio
– sancho.s ReinstateMonicaCellio

2014-01-07 18:30:02 +00:00
Commented Jan 7, 2014 at 18:30 — sancho.s ReinstateMonicaCellio
– sancho.s ReinstateMonicaCellio, Commented Jan 7, 2014 at 18:30

brettdj · Accepted Answer · 2015-10-14 06:18:42Z

5

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

edited Oct 14, 2015 at 6:18

answered Jan 8, 2014 at 0:14

brettdj

55.9k16 gold badges120 silver badges178 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Siddharth Rout Over a year ago

I use a different cleaning function but this goes into my database as well :)

Tanner Over a year ago

Brett and @Siddharth What's the difference between declaring it as a sub and a function?

MarkHone · Accepted Answer · 2014-01-07 18:30:00Z

4

I would re-write as follows:

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

I think all you are missing is the closing double-quotes after the 'Reporting\' part of your code...

edited Jan 7, 2014 at 18:30

answered Jan 7, 2014 at 18:20

MarkHone

3815 silver badges13 bronze badges

3 Comments

user3009860 Over a year ago

Actually, I'm copying your code now and its giving me an error on the "_" after FileFormat... help?

MarkHone Over a year ago

Try Removing the "_ " before the xlNormal and before CreateBackup. I've edited my suggested answer.

user3205578 Over a year ago

This code created an unreadable workbook for me using Excel 2007. I found the problem was with "FileFormat:=xlNormal". After changing this to "FileFormat:=xlOpenXMLWorkbook", the code now works correctly and creates an excel file that Excel can read. Not sure if this is due to the version of Excel im using etc, but hope this helps anyone that stumbles here with the same problem that I had.

Collectives™ on Stack Overflow

VBA macro to save excel file using path from cell

2 Answers 2

2 Comments

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Linked

Related