我正在尝试创建工作簿.xlsm的备份副本并将其另存为.xlsx
由于以下相同的问题：Run time error '1004': Copy method of worksheet class failed - Temp file issue我无法在更改文件格式的沿着使用SaveCopyAs
我的解决方法是
1.创建.xlsm文件的新副本
1.打开此新副本
1.另保存为.xlsx
1.关闭.xlsx文件
1.从步骤1中删除文件
这是我密码

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    On Error GoTo ErrorHandler:
    'define variables
    Dim backupfolder As String
    Dim strFileName As String
    Dim xlsxStrFileName As String
    Dim fullPath As String
    Dim xlsxFullPath As String
    Dim wkb As Workbook

    'get timestamp
    dt = Format(CStr(Now), "yyyymmdd_hhmmss")

    'construct full path to backup file which will be later converted to .xlsx
    backupfolder = "c:\work\excel macro\delete\"

    strFileName = "Test_iz_" & dt & ".xlsm"
    fullPath = "" & backupfolder & strFileName

    xlsxStrFileName = "Test_iz_" & dt & ".xlsx"
    xlsxFullPath = "" & backupfolder & xlsxStrFileName

    ActiveWorkbook.SaveCopyAs Filename:=fullPath

    Set wkb = Workbooks.Open(fullPath)

    wkb.Activate
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:=xlsxFullPath, FileFormat:=51 'saves the file
    Application.DisplayAlerts = True
    'Application.Wait (Now + TimeValue("00:00:03"))
    ActiveWorkbook.Close
    Kill fullPath
    Exit Sub

ErrorHandler:
    MsgBox "An error occured " & vbNewLine & vbNewLine & Err.Number & ": " & Err.Description
    MsgBox "Backup saved: " & xlsxFullPath
    ActiveWorkbook.SaveAs Filename:=fullPath

End Sub

我的问题是我总是在ErrorHandler中结束，即使我得到了预期的结果
当我注解掉第2行时

On Error GoTo ErrorHandler:

错误运行时错误'91'：调试时未设置对象变量或With块变量，它指向代码所在的行

wkb.Activate

而.xlsm文件未被删除
我猜问题是，当我创建新的xlsm文件副本并保存它，这整个代码将执行一次，该问题是存在的地方，但我找不到它.谢谢