Save As Failed Excel VBA

Question

- Summary : I am trying to write a code that will be automatically saved with the name of the current date

- The problem . Error: The "Save" method of the object "_Workbook" failed "appears when the compiler reaches the line that saves. Everything else works. I showed the whole function for links.

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 was added to the method of converting to a string into a date, because I thought it might cause a problem, but it has the same result. Let me know if you see something wrong. Thanks!

Answer 1

Problem: because in my code I disconnected the hints from excel when I tried to save, I did not see the hints telling me that I was trying to save with the wrong format.

Basically, to summarize, Excel didn't like that I had backslashes ("/") in my file name (which I really should have known)

Correction: 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 the next month, day, and year together in a line separated by underscores to avoid a malicious backslash.

Thanks for your help Guffy!

Answer 2

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 necessary.

Explanation:

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

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

This will cause your system to crash invalid characters in the file name.