Excel Excel VBA (Saving as A CSV)

[cuddihy1990]

I have a sheet that runs a macro to save the file into a directory as a csv file using the now function to receive a timestamp.

Below is a copy of the code used;

Private Sub saveasdt()
Dim dt As String
Dim location As String
dt = Now
Sheet3.SaveAs Filename:="Q:\SPREAD\Billy\" & dt & ".csv", FileFormat:=xlCSV
End Sub

An error appears when the last line of code is run;

Run-time error '1004':
Application-defined or object-defined error.

Any ideas why there is an error and can anyone provide alternative code for this.

Cheers in advance,

Billy

 

[davesexcel]

You can't saveas a date, but you can format the string to look like a date, for example.

Private Sub saveasdt()
  Dim dt As String
  Dim location As String
  dt = Format(Now, "MMMM-DD-YyyY")
  Sheet3.SaveAs Filename:="C:\Test2\" & dt & ".csv", FileFormat:=xlCSV
End Sub