-摘要:我正在尝试编写将自动保存为当前日期名称
的代码-问题:当编译器到达保存的行时,弹出错误说"对象'_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_AccountingREPORTING & CONTROLS TEAMBook And Balance_KatieChase Booking History" 'loads the crystal report
Workbooks.Open Filename:= _
"Z:Customer_Service_AccountingREPORTING & CONTROLS TEAMBook And Balance_KatieChase Booking HistoryDo_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_AccountingREPORTING & CONTROLS TEAMBook And Balance_KatieChase Booking History" & CStr(theDate), FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
Application.DisplayAlerts = True
End Function
-我在日期添加了转换为字符串方法,因为我认为这可能会导致问题,但结果相同。如果您在这里发现任何错误,请告诉我。谢谢!
问题:由于在我的代码中我禁用了来自 excel 的提示,因此当我尝试保存时,我没有看到提示告诉我我正在尝试使用不正确的格式保存。
基本上总结一下,Excel 不喜欢我的文件名中有反斜杠 ("/")(我真的应该知道)
解决方法:我最终使用了以下语句:
ActiveWorkbook.SaveAs Filename:="Z:..." & "Chase " & _
Month(theDate) & "_" & Day(theDate) & "_" & Year(theDate) & ".xlsx"
因此,我在这里真正做的只是将月、日和年一起发布到一个由下划线分隔的字符串中,以避免邪恶的反斜杠。
感谢您的帮助加菲!
你试过这样的事情吗?
ActiveWorkbook.SaveAs Filename:="Z:Customer_Service_AccountingREPORTING & CONTROLS TEAMBook And Balance_KatieChase Booking History" & Format(theDate, "mm.dd.yy"), FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
要强调:我将CStr(theDate)
更改为 Format(theDate, "mm.dd.yy") & ".xlsx"
,但如果需要,您可以使用其他格式。
解释:
theDate
的类型为 Date
(请参阅: Dim theDate As Date
),因此当您使用 CStr()
时返回的是完整的日期/时间格式字符串。这将导致如下所示的内容:
Debug.Print CStr(Now())
7/6/2012 7:23:38 AM
这可能会导致您的系统拒绝文件名中的无效字符。