CODEWITHSUNDEEP
vbaexcel
JBear
JBear

Reputation: 45

VBA Save As special character error

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

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

Upvotes: 1

Views: 1689

Answers (2)

JamesFaix
JamesFaix

Reputation: 8665

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.

Upvotes: 2

luke_t
luke_t

Reputation: 2985

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.

Upvotes: 3

Related Questions