Save date as filename

[HBorenz]

Goog Evening,

I'm trying to save my workbook with the current date as the filename.
I use this macro
Sub SaveAsA1()
Name=Range("A1").value
Activeworkbook.SaveAs Filename:=Name

I put =now() in cell A1, and format it as 9-Sep, but it does not work.

Any help would be greatly appreaciated,

Thanks
Harlan

 

[Juan Pablo González]

Change Name to

Name = Range("A1").Text

 

[Emily]

Or Simply in the macro
Name = WorksheetFunction.Text(Date, "dd-mmm")

 

[Juan Pablo González]

Emily, you could use VBA's Format function for this as well, instead of the long text for the Text worksheetfunction...

<pre>Name = Format(Date, "dd-mmmm")</pre>

 

[sagain2k]

Try this out...it will save the current date as a text value and assign it to a variable you can use for the filename. Note also that you could change the "mm" and "yy" etc.. date format characters as needed. Hope this helps! Cheers,

--Ray

Sub testdatetext()

'Saves the current sheet with the current date as the filename

Dim fname

ActiveCell.Formula = "=Text(Now(), ""m""""-""""dd""""-""""yy"")"
'This captures the current date as text into the active cell, but
'to really convert it to text that can be used in filename do these
'extra steps too

Selection.Copy 'copies the active cell

'PASTES AS TEXT ONLY VALUE THAT CAN BE USED AS A FILE OR RANGENAME LATER
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

fname = Selection 'assigns the variable fname to the selection
'fname could now be used as a filename

End Sub

 

[Juan Pablo González]

sagain2k, look above for some better/quicker ways of getting this done...

 

[sagain2k]

Thanks Juan, Emily... those methods are much simpler and very useful! Juan, when I tried the example:

Name = Range("A1").Text

for a numeric value, the result seemed to still be numeric rather than text only...

Thanks again for all the great tips!!!

-R

 

[Emily]

On 2002-09-11 20:52, Juan Pablo G. wrote:
Emily, you could use VBA's Format function for this as well, instead of the long text for the Text worksheetfunction...

<pre>Name = Format(Date, "dd-mmmm")</pre>

Thanks. I forgot the "format" function.

 

[JayMenon]

Hi !!

If the question is still unanswered. Please try the following code. It works well !!

Sub filesave()
ActiveWorkbook.SaveAs (Format(Date, "dd-mm-yy"))
End Sub

Regards !