ActiveWorkbook.SaveAs FileName:= ....variableName

[Paul Kent]

I would like to save a workbook with the File Name variableName.xls,
but haven't had any luck. The following VBA macro, which is
attempting to name the file as "employeeName.xls" (ex: PaulKent.xls),
produces a "Compile Error:Expected:End of Statement"

ActiveWorkbook.SaveAs FileName:= _
"PB 27GB HD:Documents:Programming
Docs:NWASeniorityProject:200201Project:IndividDocs:"
&employeeName".xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

If I change the code to:

ActiveWorkbook.SaveAs FileName:= _
"PB 27GB HD:Documents:Programming
Docs:NWASeniorityProject:200201Project:IndividDocs: &employeeName.xls"
_
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

I just get "&employeeName.xls" as the file name, which is not my
goal. Any feedback would be greatly appreciated. Thanks in advance.

[J.E. McGimpsey]

Your first statement needs a concatenate operator (&) between employeeName
and ".xls". With your second example, the variable needs to be outside the
quoted string, otherwise it's just treated as text, not a variable:

Dim employeeName As String
employeeName = "PaulKent"
ActiveWorkbook.SaveAs FileName:= _
"PB 27GB HD:Documents:Programming Docs:NWASeniorityProject:" & _
"200201Project:IndividDocs:" & employeeName & ".xls", _

FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

Note that I broke up the path between "NWASeniorityProject:" and
"200201Project" in order to avoid linewrap in this message - it's not
required.

On 4/4/02 09:40, in article 8a368ff4.02040...@posting.google.com,
"Paul Kent" <paul...@compuserve.com> wrote:

--
Office/Mac MVP

[Paul Kent]

Works like a charm. Wish I had discovered this forum 4 years ago when
I first got Excel 98. Thanks a million, JE.