excel - Excel VBA - 使用通配符或部分匹配打开的工作簿无法另存为副本

Question

我想使用通配符或部分名称匹配打开工作簿并使用其他名称保存副本。

但是，有一个错误——

始终在“ Workbooks(myFolderPath & "" & MyFileName).SaveCopyAs Filename:="NEW NAME.xlsx" " 行

这是我的代码：

Sub GENERATE()

Application.ScreenUpdating = False
Application.DisplayAlerts = False


'If workbook name like "Report Due" then open, if not already opened

Dim MyFileName As Variant
Dim myFolderPath As String

myFolderPath = Application.DefaultFilePath
MyFileName = Dir(myFolderPath & "\" & "Report Due*.xlsx")

If MyFileName <> "" Then
    Workbooks.Open (myFolderPath & "\" & MyFileName)
End If

Workbooks(myFolderPath & "\" & MyFileName).SaveCopyAs Filename:="NEW NAME.xlsx"
Workbooks(myFolderPath & "\" & MyFileName).Close SaveChanges:=False

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

我很乐意看看有什么问题！非常感谢！

标签： excelvba

Answer 1

打开时设置对工作簿的引用，然后在保存副本时不需要使用它的名称来引用。

Option Explicit

Sub GENERATE()
Dim wb As Workbook
Dim MyFileName As Variant
Dim myFolderPath As String

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    'If workbook name like "Report Due" then open, if not already opened

    myFolderPath = Application.DefaultFilePath
    MyFileName = Dir(myFolderPath & "\" & "Report Due*.xlsx")

    If MyFileName <> "" Then
        Set wb = Workbooks.Open(myFolderPath & "\" & MyFileName)
        wb.SaveCopyAs Filename:="NEW NAME.xlsx"
        wb.Close SaveChanges:=False
    End If

    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

End Sub