Save As failed Excel VBA

Question

-Summary: I'm trying write code that will automatically save with the name of the current date

-Problem: Error saying "Method 'SaveAs' of object '_Workbook' failed" pops up when compiler reaches the line that saves. Everything else works. I've shown the whole function for references' sake.

Function createRecord()

    Dim rowCount As Integer

    Dim theDate As Date

    theDate = Format(Now(), "MM-DD-YY")

    Sheets("New Data").Select
    Cells.Select
    Selection.Copy
    Sheets.Add After:=Sheets(Sheets.Count)
    Application.ActiveSheet.Name = "ChaseHistory"
    ActiveSheet.Paste
    rowCount = ActiveSheet.UsedRange.Rows.Count

    Sheets("Exceptions").Select
    'rowCount = ActiveSheet.UsedRange.Rows.Count
    Application.CutCopyMode = False
    ActiveSheet.UsedRange.Rows.Select
    Selection.Copy
    Sheets("ChaseHistory").Select
    ActiveSheet.Range("A" & rowCount + 2).Select
    ActiveSheet.Paste
    Range("A1").Select
    Cells.Select
    Selection.Copy

    ChDir "Z:\Customer_Service_Accounting\REPORTING & CONTROLS TEAM\Book And Balance_Katie\Chase Booking History"       'loads the crystal report

    Workbooks.Open Filename:= _
        "Z:\Customer_Service_Accounting\REPORTING & CONTROLS TEAM\Book And Balance_Katie\Chase Booking History\Do_Not_Delete.xlsx"

    Windows("Do_Not_Delete").Activate
    ActiveSheet.Paste

    Application.DisplayAlerts = False
                 '---------------This is the problem child--------------                                                                  'SAVING WORKBOOK
    ActiveWorkbook.SaveAs Filename:="Z:\Customer_Service_Accounting\REPORTING & CONTROLS TEAM\Book And Balance_Katie\Chase Booking History\" & CStr(theDate), FileFormat:= _
    xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
    , CreateBackup:=False

    Application.DisplayAlerts = True

End Function

-I added in the convert to string method on date because I thought that might be causing the problem but had the same result. Let me know if you see anything wrong here. Thanks!

score 10 · Accepted Answer · edited Sep 04 '12 at 17:36

10

The Problem: because in my code I was disabling prompts from excel, when I was trying to save I wasn't seeing a prompt telling me that I was attempting to save with an improper format.

Basically to sum it up, Excel didn't like that I had backslashes ("/") in my filename (which I really should have known)

The Fix: I ended up using this statement:

ActiveWorkbook.SaveAs Filename:="Z:...\" & "Chase " & _
    Month(theDate) & "_" & Day(theDate) & "_" & Year(theDate) & ".xlsx"

So all I really did here was post month, day, and year together into a string separated by underscores to avoid the evil backslash.

Thanks for your help Gaffi!

edited Sep 04 '12 at 17:36

David Mulder

26,123
9
51
114

answered Jul 06 '12 at 14:56

Mike Kellogg

1,168
5
15
34

2

don't forget you can accept your own answer. also, you can use `replace(theDate,"/","_")` if you want to reduce the length of the line – SeanC Aug 08 '12 at 13:39
@SeanCheshire thanks, I forgot to select the answer as solved. Good comment :D – Mike Kellogg Aug 09 '12 at 13:24

Gaffi · Answer 2 · 2013-07-09T20:05:07.540

4

Have you tried something like this?

ActiveWorkbook.SaveAs Filename:="Z:\Customer_Service_Accounting\REPORTING & CONTROLS TEAM\Book And Balance_Katie\Chase Booking History\" & Format(theDate, "mm.dd.yy"), FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False

To highlight: I changed CStr(theDate) to Format(theDate, "mm.dd.yy") & ".xlsx", but you can use other formats if needed.

Explanation:

theDate is of type Date (see: Dim theDate As Date), so what is returned is a complete date/time format string when you use CStr(). This will result in something like this:

Debug.Print CStr(Now()) 
7/6/2012 7:23:38 AM

Which will likely cause your system to reject for invalid characters in the filename.

edited Jul 09 '13 at 20:05

answered Jul 06 '12 at 14:22

Gaffi

4,307
8
43
73

I already formatted the date above though. Do you think formatting it in-line would help? – Mike Kellogg Jul 06 '12 at 14:24
Dim theDate As Date theDate = Format(Now(), "MM-DD-YY") – Mike Kellogg Jul 06 '12 at 14:26
@MikeKellogg I do see that - your code should be good in that regard. I also added the file extension, however. I have not tested all options, but that may also have something to do with it. – Gaffi Jul 06 '12 at 14:26
ah, that is a good point. Let me try using the file extension – Mike Kellogg Jul 06 '12 at 14:27
Let me ask you this as well... Does it work with just `ActiveWorkbook.SaveAs "Z:\Customer_Service_Accounting\REPORTING & CONTROLS TEAM\Book And Balance_Katie\Chase Booking History\" & Format(theDate, "mm.dd.yy")` (i.e. no options other than file name set)? – Gaffi Jul 06 '12 at 14:32
ok Gaffi I tried changing fileformat to xlWorkbookNormal and it didn't work still. Let me try taking off the extra options other than file name set now and see what happens – Mike Kellogg Jul 06 '12 at 14:39
3

@MikeKellogg Despite your `theDate = Format(Now(), "MM-DD-YY")` solves this problem, it is a coincidence it does so. You cannot store format in a variable of type `Date`. What you code does is converting date to string while truncating the time, then parsing the string back to a `Date`. All that is wrong and fragile, and will fail if current system date format a) is different from `MM-DD-YY` (will swap month and day when reading back from string) or b) does not omit the time part when it's all zeros (will output time anyway into the file name). Only keep `Format` when generating the file name. – GSerg Jul 06 '12 at 15:07
@GSerg So you think I should change the "theDate" variable's data type to String instead of date? – Mike Kellogg Jul 06 '12 at 15:13
@MikeKellogg If you later don't use it as a `date`, and only use it as a part of a file name, then yes, change to `String`. – GSerg Jul 06 '12 at 15:17
@GSerg Tested with variable as a string and it worked fine. Thanks for helping me solidify my code some. Good comment :D – Mike Kellogg Jul 06 '12 at 15:31

Save As failed Excel VBA

2 Answers2

Linked