另存为Excel VBA失败 [英] Save As failed Excel VBA

问题描述

- 摘要：
我正在尝试使用当前日期名称自动保存的写代码

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

- 问题：当编译器到达保存行时，弹出对话框_Workbook失败的方法SaveAs错误。一切工作。

-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

- 我在日期转换为字符串方法，因为我认为这可能是导致问题，但有相同的结果，让我知道，如果你在这里看到任何错误谢谢！ p>

-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!

推荐答案

问题：因为在我的代码中，我正在禁用excel提示，当我试图保存我没有看到一个提示告诉我，我试图以不正确的格式保存。

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)

修复：我最终使用这个语句： p>

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.

感谢您的帮助Gaffi！

Thanks for your help Gaffi!

这篇关于另存为Excel VBA失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！