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

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
 
D

Dave Peterson

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top