strip macros from new xlsx file

Question

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

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

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

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

when you do `set wb2 = ActiveWorkbook` you're setting the reference to wb2 to be the same as wb, since they both reference your current active workbook. Instead do `set wb = ThisWorkbook` and copy the contents of the sheet to a new empty sheet instead of the whole sheet. If this is not an option and you must copy the whole sheet without code, this question might help https://stackoverflow.com/questions/32016718/copy-a-worksheet-without-copying-the-code — Fernando J. Rivera, Dec 12 '20 at 21:14

score 1 · Answer 1 · answered Dec 12 '20 at 21:17

You could delete the sheetChange event script from the original sheet and set it on the Workbook instead, that way the sheet is clean and you can copy it easily. Put this in your ThisWorkbook scripts page:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    set Sh = worksheets(1) 'HERE you choose the worksheet where you want to run this code
    If Sh.target.Address = "$C$2" And Not Sh.target.Value = "Company" Then
        newProspect "focus" ' causes error because this is not found in the xlsx
    End If
End Sub

There's no guarantee they'll both be open at the same time. However, I might do something like this adding a 'poison pill' to the new sheet to have the macro not trigger (eg, "<<<" in Cell A1). That's what I'm doing now. — Jason Torpy, Dec 14 '20 at 19:14

score 1 · Accepted Answer · answered Dec 12 '20 at 23:19

the file stays open for editing.

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

Way 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

Way 2

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

Click File --> Options.
In the navigation pane, select Trust Center.
Click Trust Center Settings....
In the navigation pane, select Macro Settings.
Ensure that Trust access to the VBA project object model is checked.
Click OK.

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: I prefer Way 1 but then that is just my personal preference.

both good answers. way2 requires excess user input per my original post. way2 would be ok but filepath can't be a static string. any way to grab the filepath of wb dynamically and make that the same as wb2? I need to be able to open it up dynamically... — Jason Torpy, Dec 14 '20 at 19:12
FilePath = "C:\SampleFolder\New Form.xlsx" this is a static address. I want something that will change based on the original File Path of Workbook wb — Jason Torpy, Dec 14 '20 at 22:26

strip macros from new xlsx file

2 Answers2

Linked