View Full Version : Solved: Naming a File
ajrob
03-08-2009, 08:08 PM
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:
' 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
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
03-09-2009, 02:08 AM
' 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
ajrob
03-09-2009, 10:05 AM
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
03-09-2009, 10:09 AM
' 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
ajrob
03-09-2009, 11:52 AM
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
03-09-2009, 01:40 PM
Sorry, messed up my brackets
' 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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.