从新的xlsx文件中删除宏 [英] strip macros from new xlsx file

问题描述

功能部分:以下代码将2个选项卡从xlsm文件保存到新的xlsx文件.该文件将保持打开状态以供编辑.

FUNCTIONAL PORTION: The code below saves 2 tabs from an xlsm file to a new xlsx file. the file stays open for editing.

错误:xlsm选项卡的工作表代码中有一个触发器.一旦在xlsx工作表中输入任何内容，该触发器将无效并导致错误.

ERROR: The xlsm tab has a trigger in the sheet code. That trigger is invalid and causes an error as soon as anything is entered in the xlsx sheet.

所需的输出:编辑新文件时没有错误

DESIRED OUTPUT: no error generated when editing the new file

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

FAILED FIX ATTEMPT: I tried to use Scripting to delete the macros but the brand new sheet doesn't give access to its code. I might have done that wrong...

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"标签中

Below is the sheet macro that triggers on editing the new xlsx Saved in Pro Focus tab

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

推荐答案

文件保持打开状态以供编辑.

the file stays open for editing.

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

There are two ways that I can immediately think of to handle this situation.

方法1

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

You need to close and re-open the newly created file.

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

方法2

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

Delete the VBA code from the newly created file. For this you need to ensure that Trust access to the VBA project object model is checked by doing the following

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

代码:

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

注意:我更喜欢方法1 ，但这只是我个人的喜好.

Note: I prefer Way 1 but then that is just my personal preference.

这篇关于从新的xlsx文件中删除宏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！