CODEWITHSUNDEEP
excelsavevba
ARich
ARich

Reputation: 3279

VBA SaveAs Method

After browsing forums for well over an hour, I can't seem to figure out why my save as code isn't working.

My goal is to save a new copy of the workbook under a different file type. (The current file type is .csv.) I'm not trying to save in a new or different location, the current directory is where I want it to save to.

I've tried more variations of the following code than I can remember, so I'll just post my current syntax:

CurrentDir = CurDir()

dateVal = Date

ActiveWorkbook.SaveAs Filename:="" & CurrentDir & "ALS Week of " & dateVal - 4 & ".xlsx", FileFormat:=51

I've looked at examples of how to open a dialog box wherein the user inputs information in order to save. But I'm hoping for a hands-off approach. If anyone can see where my error lies, please let me know!

EDIT: The error that I get is "Method 'SaveAs' of object '_Workbook' failed

Upvotes: 1

Views: 3142

Answers (2)

Brad
Brad

Reputation: 12245

Your date has illegal characters in it. Format your date with dashes and not slashes and this won't happen.

The following reserved characters:

    < (less than)
    > (greater than)
    : (colon)
    " (double quote)
    / (forward slash)
    \ (backslash)
    | (vertical bar or pipe)
    ? (question mark)
    * (asterisk)

Upvotes: 5

Hauke P.
Hauke P.

Reputation: 2823

You forgot a backslash:

ActiveWorkbook.SaveAs Filename:= CurDir() & "\ALS Week of " & (Date - 4) & ".xlsx", FileFormat:=51

Upvotes: 1

Related Questions