Save As: Today's Date

Posted by SN on March 28, 2001 1:35 PM

I have put together a Macro that will copy a page from a work sheet, paste it to another worksheet, and then save that new worksheet as a CSV file. I would like the macro to save the file as the current day's date. If this is possible, does anybody know the command to use within Excel's Visual Basic Editor that will do this? I need this because if the Macro has a "static" file name, it will "bug out" when it's time to save the file with a name that already exists.

Thanks.

Posted by Dave Hawley on March 28, 2001 2:18 PM

Hi SN

Ty this, untested code:

Sub SaveAsdate()
Dim Tdy As Stringn
Tdy = Date & ".csv"
ActiveSheet.SaveAs FileName:=Tdy, FileFormat:=xlCSV
End Sub

Dave
OzGrid Business Applications

Posted by SN on March 28, 2001 4:08 PM

Dave,

No luck with above code... i tried it a few different ways in the existing Macro i had and it just bugged out each time. Any other suggestions? I'm sure it's right under our noses!

Thanks.

Posted by Dave Hawley on March 28, 2001 4:26 PM

I take it you spotted my typo e.g "Stringn"

It should work

Sub SaveAsdate()
Dim Tdy As String
Tdy = Date & ".csv"
ActiveSheet.SaveAs FileName:=Tdy, FileFormat:=xlCSV
End Sub

....Or

Sub SaveAsdate()
Dim Tdy As String
Application.DisplayAlerts = False
Tdy = Date & ".csv"
ActiveWorkbook.SaveAs FileName:=Tdy, FileFormat:=xlCSV
Application.DisplayAlerts = True
End Sub

This one will save the activesheet also.

If you are getting Run time errors, what are they ?

Dave

OzGrid Business Applications

Posted by SN on March 28, 2001 5:38 PM

Dave,

This is what i put in:

Sub SaveAsdate()
Dim Tdy As String
Tdy = Date & ".csv"
ActiveSheet.SaveAs FileName:=Tdy,
FileFormat:=xlCSV
End Sub

The error is that "01.csv can't be accessed. the file may be read only, or you may be trying to access a read only location, or the server the document is sotred on may not be responding"

The line: ActiveSheet.SaveAs FileName:=Tdy,
FileFormat:=xlCSV is highlighted when try to debug.

Thanks.

Posted by Dave Hawley on March 28, 2001 8:11 PM

Sounds like you are on a network ?

You may have to include the file path where you want the .csv file saved. Or go to Tools>Options|General and change the default file path that Excel is saving to.

Dave
OzGrid Business Applications

Posted by Edgar on March 29, 2001 2:06 AM

The problem is that you cannot save a file as "Date" because Date includes "slashes" which are not acceptable in a file name.
Try changing the code to reformat the Date to something like "mm.dd.yy"

Posted by Ed on March 29, 2001 2:23 AM

Something like :- Tdy = Format(Date, "dd.mm.yy") & ".csv"

Posted by David Hawley on March 29, 2001 3:13 AM

Sorry SN, brain death.

Of course, SN that is most likely the reason. As I was on a read only system today I was simply using the MsgBox function to return the string and the format for the PC was dd-mm-yyyy. I will quietly slip away