Saving Excel 97 files

misterg · May 15th, 2002, 03:34 AM

Right, hopefully this is a straightforward one!

To cut a long story short, i need to automate the saving on an Excel workbook to add a cell value into the file name.

For example...the file saves itself as "myfile.xls"...this is fine.
However, the same file will contain different data values every week, so i need a "Week commencing" cell value or date code added automatically to the filename.

For example, one week it will be myfile15-4-02.xls, the next week it will be myfile22-4-02.xls

Can this be done?

Many thanks

Mudface · May 15th, 2002, 03:40 AM

Would something like this be useful for you (use in a standard module, or even in the Workbook BeforeClose module): -

ChDir "C:My Documents"
ThisWorkbook.SaveAs Format(Date, "dd-mm-yy")

Rather than using the ChDir command you could build up the FileName from a string used for the path, and just concatenate the various bits together. HTH.

misterg · May 15th, 2002, 09:33 AM

Mudface,

Busy day so far!

I have yet to try this idea, but many thanks in advance for taking a look!

I will let you know how i get on.

G

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 15th, 2002, 03:34 AM	#1
misterg Board Regular Join Date: May 2002 Posts: 66	Right, hopefully this is a straightforward one! To cut a long story short, i need to automate the saving on an Excel workbook to add a cell value into the file name. For example...the file saves itself as "myfile.xls"...this is fine. However, the same file will contain different data values every week, so i need a "Week commencing" cell value or date code added automatically to the filename. For example, one week it will be myfile15-4-02.xls, the next week it will be myfile22-4-02.xls Can this be done? Many thanks

May 15th, 2002, 03:40 AM	#2
Mudface MrExcel MVP Join Date: Feb 2002 Location: Sunny, spring-like Hull Posts: 3,339	Would something like this be useful for you (use in a standard module, or even in the Workbook BeforeClose module): - ChDir "C:My Documents" ThisWorkbook.SaveAs Format(Date, "dd-mm-yy") Rather than using the ChDir command you could build up the FileName from a string used for the path, and just concatenate the various bits together. HTH.

May 15th, 2002, 09:33 AM	#3
misterg Board Regular Join Date: May 2002 Posts: 66	Mudface, Busy day so far! I have yet to try this idea, but many thanks in advance for taking a look! I will let you know how i get on. G