Solved: Naming a File

[ajrob]

I have an excel macro that is intended to save a subset of data to a report under a unique, yet deliberate file name. Here's an excerpt:

[VBA]' Creates a temporary base file name.
Const FILE_NAME As String = _
"S:\Open Provisioning Reports\0903 2009 Open Provisioning - Pkg.xls"

ActiveWorkbook.SaveAs _
Filename:=Replace(FILE_NAME, "<date>", Format(Date, "yymm")), _
FileFormat:=xlNormal
ActiveWindow.Close
[/VBA]
In the code above this, I declared a variable (string) named IB that contains the value of the year -- 2009 in the example above.

I save a new copy of this file at the beginning of each new month, so ideally I'd like the macro to look at the current system date and express that as yymm -- 0903 in the example above.

The code I've got isn't cutting it -- any pointers?

[Bob Phillips]

[vba]

' Creates a temporary base file name.
Const FILE_NAME As String = _
"S:\Open Provisioning Reports\<date> Open Provisioning - Pkg.xls"

ActiveWorkbook.SaveAs _
Filename:=Replace(FILE_NAME, "<date>", Format(Date, "yymm yyyy")), _
FileFormat:=xlNormal
ActiveWindow.Close
[/vba]

[ajrob]

This works great, except that yyyy is not always the current year. Instead, it is declared in an Input Box and saved to a variable named IB (dimensioned as a string if that makes a difference). Typical values are 2008, 2009, and 2010.

So, instead of:

Const FILE_NAME As String = _
"S:\Open Provisioning Reports\<date> Open Provisioning - Pkg.xls"

ActiveWorkbook.SaveAs _
Filename:=Replace(FILE_NAME, "<date>", Format(Date, "yymm yyyy")), _
FileFormat:=xlNormal
ActiveWindow.Close

Can I incorporate the string variable into the file name much the same as I incorporate <date> with the code above?

[Bob Phillips]

[vba]


' Creates a temporary base file name.
Const FILE_NAME As String = _
"S:\Open Provisioning Reports\<date> <year> Open Provisioning - Pkg.xls"

ActiveWorkbook.SaveAs _
Filename:=Replace(Replace(FILE_NAME, "<date>", Format(Date, "yymm"),"<year>",IB)), _
FileFormat:=xlNormal
ActiveWindow.Close
[/vba]

[ajrob]

I have a compile error now:

"Argument not optional." Text in red is highlighted with the error??

Const FILE_NAME As String = _
"S:\Open Provisioning Reports\<date> <year> Open Provisioning - Pkg.xls"

ActiveWorkbook.SaveAs _
Filename:=Replace(Replace(FILE_NAME, "<date>", Format(Date, "yymm"),"<year>",IB)), _
FileFormat:=xlNormal
ActiveWindow.Close

[Bob Phillips]

Sorry, messed up my brackets

[vba]

' Creates a temporary base file name.
Const FILE_NAME As String = _
"S:\Open Provisioning Reports\<date> <year> Open Provisioning - Pkg.xls"

ActiveWorkbook.SaveAs _
Filename:=Replace(Replace(FILE_NAME, "<date>", Format(Date, "yymm")), "<year>", IB), _
FileFormat:=xlNormal
ActiveWindow.Close
[/vba]