Excel - How to add Date Stamp to SaveAs "file name" within VBA for Excel?

Asked By fisch4bil on 12-Aug-09 03:40 PM
I have written some VBA code to pull several columns from an Excel
spreadsheet and save these in their own file of *.csv format. However, the
end users would greatly benefit from having the saved date as part of the
filename. I have tried several different methods in my code, but, none are
working.  The rest of the code works as intended (I get the new file created
and saved with the new filename).

The relevant code is as follows:

Workbooks.Add
Windows(Sheet_Name).Activate
Cells.Select
Selection.Copy
Workbooks("Book1").Worksheets("Sheet1").Activate
Cells.Select
ActiveSheet.Paste
Sheets(Sheets.Count).Name = "Crimson_Info"
ActiveWorkbook.SaveAs
Filename:="\\Cmc-thor\Infosystems\Projects\Crimson\Crimson_Info.csv",
FileFormat:=xlCSV




Dave Peterson replied on 12-Aug-09 03:47 PM
ActiveWorkbook.SaveAs _
Filename:="\\Cmc-thor\Infosystems\Projects\Crimson\Crimson_Info" _
& format(date,"yyyy-mm-dd") & ".csv", _
FileFormat:=xlCSV

Or include the date and time????

ActiveWorkbook.SaveAs _
Filename:="\\Cmc-thor\Infosystems\Projects\Crimson\Crimson_Info" _
& format(now,"yyyymmdd--hhmmss") & ".csv", _
FileFormat:=xlCSV

Remember that filenames cannot use colons or slashes in their names--that is why
the Format() stuff did not include them.


--

Dave Peterson