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

  • Thread starter Thread starter fisch4bill
  • Start date Start date
F

fisch4bill

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've 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
 
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 can't use colons or slashes in their names--that's why
the Format() stuff didn't include them.
 
Back
Top