从新的 xlsx 文件中删除宏

功能部分：下面的代码将 xlsm 文件中的 2 个选项卡保存到新的 xlsx 文件中。文件保持打开状态以进行编辑。

错误：xlsm 选项卡在工作表代码中有触发器。该触发器无效，一旦在 xlsx 工作表中输入任何内容，就会导致错误。

所需输出：编辑新文件时未生成错误

修复尝试失败：我尝试使用脚本删除宏，但全新的工作表无法访问其代码。我可能做错了...

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

文件保持打开状态以进行编辑。

我可以立即想到两种方法来处理这种情况。

Way 1

您需要关闭并重新打开新创建的文件。

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

Way 2

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

1. 单击文件 --> 选项。
2. 在导航窗格中，选择信任中心。
3. 单击信任中心设置...。
4. 在导航窗格中，选择宏设置。
5. 确保选中“信任对 VBA 项目对象模型的访问”。
6. 单击“确定”。

Code:

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

Note： 我更喜欢Way 1但这只是我个人的偏好。