Macro save as CSV with Date not working

[oliviar]

Hi Guys,

Why does my macro hate me?

    ChDir "P:\Data\Data Sent"
    ActiveWorkbook.SaveAs Filename:= _
        "P:\Data\Data Sent\AU2011" & Format(Date, “yyyymmdd“) & “.csv”_
        FileFormat:=xlCSV, CreateBackup:=False

I keep getting a syntax error on the FileFormat line.

I am using 2007

 

[xenou]

I think you need a space between the underscore and the last quotation mark on the third line:

    ChDir "P:\Data\Data Sent"
    ActiveWorkbook.SaveAs Filename:= _
        "P:\Data\Data Sent\AU2011" & Format(Date, “yyyymmdd“) & “.csv”[COLOR="Red"][B]_[/B][/COLOR]
        FileFormat:=xlCSV, CreateBackup:=False

Amend to:

    ChDir "P:\Data\Data Sent"
    ActiveWorkbook.SaveAs Filename:= _
        "P:\Data\Data Sent\AU2011" & Format(Date, “yyyymmdd“) & “.csv”[COLOR="Red"][B] _[/B][/COLOR]
        FileFormat:=xlCSV, CreateBackup:=False

Or:

    ChDir "P:\Data\Data Sent"
    ActiveWorkbook.SaveAs Filename:= _
        "P:\Data\Data Sent\AU2011" & Format(Date, “yyyymmdd“) & “.csv” FileFormat:=xlCSV, CreateBackup:=False

 

[xenou]

Edit:
Oops. A space and a comma are needed:

    ChDir "P:\Data\Data Sent"
    ActiveWorkbook.SaveAs Filename:= _
        "P:\Data\Data Sent\AU2011" & Format(Date, “yyyymmdd“) & “.csv”[COLOR="Red"][B], _[/B][/COLOR]
        FileFormat:=xlCSV, CreateBackup:=False

Or:

    ChDir "P:\Data\Data Sent"
    ActiveWorkbook.SaveAs Filename:= _
        "P:\Data\Data Sent\AU2011" & Format(Date, “yyyymmdd“) & “.csv”, FileFormat:=xlCSV, CreateBackup:=False

 

[oliviar]

Such picky little things!

 

[oliviar]

Sigh, but now when I run it is says 'object required'
On the filename line its just **** unhappy.

 

[oliviar]

Okay, I bashed it into this and its working:

On Error GoTo End1:

  fname = "P:\Data\Data Sent\AU" & Format(Date, "YYYYMMDD") & ".csv"
    ActiveWorkbook.SaveAs Filename:=fname, FileFormat:=xlCSVWindows
    ActiveWorkbook.Close SaveChanges:=False

End1:
End Sub