MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Need help w/ variable savefile with specific date format


Posted by Thomas M on December 01, 2001 8:52 AM

Hey everyone out there. This may be a simple problem, but as of yet the answer has eluded me:

I'm trying to save a file by using a cell reference that updates with the current day's date in the format "MyFileMMDDYY" (no spaces). I'm using the following code (Thanks to another post on this board for the code sample, though I cannot find the message again to give proper credit!):

Public Sub SaveAsA1()
ThisFile = Range("A1").Value
ActiveWorkbook.SaveAs Filename:=ThisFile
End Sub

I've tried making cell A1 =today() , then custom formatting the cell as "C:\MyFilePath\MyFile"MMDDYY , which returns the correct file path (and format)into the cell, but when I attempt to run the macro, I receive an error. I've also tried actually typing in the correct date format (with the above cell custom formatting), and the macro works with this.


I think the issue is getting past the formatting. I cannot figure out a way to input a =today() function into a cell, and read the *formatted* value into another (I get Excel date values). If I can do this, I think I'll be set.

Any suggestions?

Thanks;
- Thomas


Posted by Luke on December 01, 2001 11:10 AM

Hi,
Don't know exactly, but you might try making 'thisfile' a string : str(thisfile) or str(range("a10").value)
luke

Posted by Andrew Cheung on December 02, 2001 7:01 AM


Thomas:
In Range("A1"), type in
=text(today(),"MMDDYY")

In the Macro:


It should be working

Andrew

Posted by Andrew Cheung on December 02, 2001 7:02 AM

Thomas:
In Range("A1"), type in
=text(today(),"MMDDYY")

In the Macro:


It should be working

Andrew

Posted by Thomas M on December 06, 2001 10:05 AM

Andrew-

FANTASTIC! Works like a charm. I really appreciate the help on this one.

- Thomas