excel - 从新的 xlsx 文件中删除宏

Question

功能部分:下面的代码将 xlsm 文件中的 2 个选项卡保存到新的 xlsx 文件中。该文件保持打开状态以进行编辑。
错误:xlsm 选项卡在工作表代码中有触发器。该触发器无效，并在 xlsx 表中输入任何内容后立即导致错误。
DESIRED OUTPUT:编辑新文件时没有产生错误
修复尝试失败:我尝试使用脚本来删除宏，但全新的工作表无法访问其代码。我可能做错了...

Sub seedPro()
    
    Dim wb As Workbook
    Set wb = ActiveWorkbook
    
    Dim wb2 As Workbook ' for new workbook
    
    ' make new sheet/names
    Worksheets(Array("Pro Focus", "AF-LU")).Copy
    
    Set wb2 = ActiveWorkbook
    wb2.SaveAs Filename:="New Form.xlsx", FileFormat:=xlOpenXMLWorkbook
        
End Sub

下面是在编辑新 xlsx 时触发的工作表宏
保存在 Pro Focus 选项卡中

Private Sub Worksheet_Change(ByVal target As Range)
    If target.Address = "$C$2" And Not target.Value = "Company" Then
        newProspect "focus" ' causes error because this is not found in the xlsx
    End If
End Sub

Answer 1

the file stays open for editing.

我可以立即想到两种方法来处理这种情况。
方式一
您需要关闭并重新打开新创建的文件。

Option Explicit

Sub WayOne()
    Dim wb As Workbook
    Set wb = ThisWorkbook
    
    wb.Worksheets(Array("Pro Focus", "AF-LU")).Copy
    
    Dim wb2 As Workbook
    Set wb2 = Application.Workbooks.Item(Application.Workbooks.Count)
    
    Dim FilePath As String
    FilePath = "C:\SampleFolder\New Form.xlsx"
    
    Application.DisplayAlerts = False
    wb2.SaveAs Filename:=FilePath, FileFormat:=xlOpenXMLWorkbook
    Application.DisplayAlerts = True
    
    wb2.Close (False)
    
    Set wb2 = Workbooks.Open(FilePath)
End Sub

方式二
从新创建的文件中删除 VBA 代码。为此，您需要确保通过执行以下操作检查对 VBA 项目对象模型的信任访问

单击文件 --> 选项。

在导航 Pane 中，选择信任中心。

单击信任中心设置...。

在导航 Pane 中，选择宏设置。

确保选中对 VBA 项目对象模型的信任访问。

单击确定。

代码 :

Option Explicit

Sub WayTwo()
    Dim wb As Workbook
    Set wb = ThisWorkbook
    
    wb.Worksheets(Array("Pro Focus", "AF-LU")).Copy
    
    Dim wb2 As Workbook
    Set wb2 = Application.Workbooks.Item(Application.Workbooks.Count)
    
    Dim FilePath As String
    FilePath = "C:\SampleFolder\New Form.xlsx"
        
    Application.DisplayAlerts = False
    wb2.SaveAs Filename:=FilePath, FileFormat:=xlOpenXMLWorkbook
    Application.DisplayAlerts = True
    
    Dim i As Long

    On Error Resume Next
    With wb2.VBProject
        For i = .VBComponents.Count To 1 Step -1
            .VBComponents.Remove .VBComponents(i)
            .VBComponents(i).CodeModule.DeleteLines _
            1, .VBComponents(i).CodeModule.CountOfLines
        Next i
    End With
    On Error GoTo 0
End Sub

备注 : 我更喜欢 方式一但这只是我个人的喜好。