VBA Save As special character error

Question

This code below was running perfect. For internal report tracking purposes I've appended "[1944] " to the front of the file name. It's now kicking back "Run-time error '1004'" at me.

Error 1004 Checklist

• Folder exists
• File is not read-only
• Len(file path) < 218

Last section references not using the following characters < > ? [ ] : | or *

But if I go to the folder location I can rename it using [ ] no problem. I can even title a file with this and launch it using VBA So why can't I save a file with it in.

Anyone got a work around for me?

Many thanks!

ActiveWorkbook.SaveAs Filename:= _
    strfilepath & "[1944] TSA34_MI_" & strdt & ".xlsb" _
    , FileFormat:=xlExcel12, Password:="", WriteResPassword:="", _
    ReadOnlyRecommended:=False, CreateBackup:=False

Answer 1

The only workaround I can think of is renaming the file once it is closed.

To do this you can use the Name statement. See MSDN documentation.

Name "C:\Users\lturner\Documents\myWorkbook.xlsb" As "C:\Users\lturner\Documents\[1]myWorkbook.xlsb"

Aside from this, I don't think it's possible to save the workbook with square brackets in the name.

Answer 2

Square brackets are legal characters for windows file names, but are treated specially by Excel, and hence VBA.

To see why, open two workbooks and in the first pick a cell and enter the formula "=SUM(", then click a cell in the second workbook. Your formula will now look something like this "=SUM([Workbook2]Sheet1!A1)". Square brackets are used as workbook name delimiters.