另存为失败的 Excel VBA
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11364007/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Save As failed Excel VBA
提问by Mike Kellogg
-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.
-问题：当编译器到达保存行时，会弹出错误提示“对象 '_Workbook' 的方法 'SaveAs' 失败”。其他一切都有效。为了参考，我已经展示了整个函数。
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!
- 我在 date 上添加了转换为字符串的方法，因为我认为这可能会导致问题，但结果相同。如果您发现这里有任何问题，请告诉我。谢谢！
回答by Mike Kellogg
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.
问题：因为在我的代码中我禁用了来自 excel 的提示，当我尝试保存时，我没有看到提示告诉我我试图以不正确的格式保存。
Basically to sum it up, Excel didn't like that I had backslashes ("/") in my filename (which I really should have known)
基本上总结一下，Excel 不喜欢我的文件名中有反斜杠（“/”）（我真的应该知道）
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!
感谢您的帮助加菲！
回答by Gaffi
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.
突出显示：我更改CStr(theDate)为Format(theDate, "mm.dd.yy") & ".xlsx"，但您可以根据需要使用其他格式。
Explanation:
解释：
theDateis 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:
theDate是类型Date（请参阅：）Dim theDate As Date，因此当您使用CStr(). 这将导致如下结果：
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.
这可能会导致您的系统拒绝文件名中的无效字符。
