Excel VBA复制按钮功能时触发Run Time error 50035错误求助

问题概述 #

要实现以下功能：

• 从原工作簿的“Hub”工作表复制A列至AK列数据到新工作簿
• 在新工作簿创建“Save the File”按钮并赋予原工作簿CommandButton1_Click的相同功能
• 导出原宏代码并导入到新工作簿，最终保存为宏启用工作簿

执行代码时触发错误：Run Time error 50035: Method of export of object '__VBComponents' failed

错误原因分析 #

1. 文件路径语法错误：原代码中filePath变量的双引号嵌套错误，导致生成的导出路径无效
2. VBComponent目标错误：导出的Sheet1可能并非宏CommandButton1_Click所在的模块/工作表，导致无法找到对应组件进行导出
3. Excel信任中心限制：未启用“信任对VBA项目对象模型的访问”权限，无法操作VBProject组件

修复方案及完整代码 #

步骤1：修正文件路径 #

将原代码中错误的路径写法：

filePath = ""C:\Users\elyon\OneDrive\Desktop\TRYING PLEASE""

改为正确的单双引号写法：

filePath = "C:\Users\elyon\OneDrive\Desktop\TRYING PLEASE\"

步骤2：确认宏所在的VBComponent #

找到CommandButton1_Click宏实际所在的位置：

• 如果宏在Hub工作表的代码模块中，导出时使用wb1.VBProject.VBComponents("Hub")
• 如果宏在标准模块（比如Module1）中，使用wb1.VBProject.VBComponents("Module1")
• 如果宏在ThisWorkbook模块中，使用wb1.VBProject.VBComponents("ThisWorkbook")

步骤3：启用VBA项目对象模型访问权限 #

打开Excel选项 → 信任中心 → 信任中心设置 → 宏设置 → 勾选“信任对VBA项目对象模型的访问”

修复后的完整代码 #

Sub CommandButton1_Click()
    Dim wb1 As Workbook
    Dim ws1 As Worksheet
    Dim wb2 As Workbook
    Dim filePath As String
    Dim fileName As String
    
    Set wb1 = ThisWorkbook
    Set ws1 = wb1.Sheets("Hub")
    
    ' 修正路径的引号错误
    filePath = "C:\Users\elyon\OneDrive\Desktop\TRYING PLEASE\"
    
    fileName = Format(ws1.Range("B2"), "DD-MMM-YYYY") & "-" & ws1.Range("C2") & ".xlsm"
    
    ws1.Range("A:AK").Copy
    
    Set wb2 = Workbooks.Add
    
    With wb2.Sheets(1).Range("A1")
        .PasteSpecial xlValues
        .PasteSpecial xlFormats
    End With
    Application.CutCopyMode = False ' 清除复制状态
    
    ' 创建按钮
    Dim newButton As Object
    Set newButton = wb2.Sheets(1).Buttons.Add(10, 10, 100, 30)
    newButton.Name = "CommandButton_Save"
    newButton.Caption = "Save the File"
    
    ' OnAction指向新工作簿中的导入宏
    newButton.OnAction = "CommandButton1_Click"
    
    On Error GoTo Cleanup ' 添加错误捕获
    
    ' 导出宏所在的VB组件（这里假设宏在Hub工作表，根据实际情况修改）
    wb1.VBProject.VBComponents("Hub").Export filePath & "ExportedCode.bas"
    
    ' 导入代码到新工作簿
    wb2.VBProject.VBComponents.Import filePath & "ExportedCode.bas"
  
    Application.DisplayAlerts = False
    wb2.SaveAs fileName:=filePath & fileName, FileFormat:=52 ' 52对应xlOpenXMLWorkbookMacroEnabled
    Application.DisplayAlerts = True
    
Cleanup:
    ' 清理临时导出的文件
    If Dir(filePath & "ExportedCode.bas") <> "" Then
        Kill filePath & "ExportedCode.bas"
    End If
    
    wb2.Close SaveChanges:=False
    Set wb1 = Nothing
    Set wb2 = Nothing
    Set ws1 = Nothing
    Set newButton = Nothing
End Sub

额外注意事项 #

• 导出后记得删除临时的ExportedCode.bas文件，避免残留
• 如果宏所在的组件是工作表，导入后新工作簿的工作表名称可能不同，需要调整代码确保宏能正常运行
• 确保目标文件夹存在，否则会触发路径不存在的错误

内容的提问来源于stack exchange，提问作者loloel