Duplicate workbook then delete named modules from the duplicate workbook

[StillUnderstanding]

Hello,

I wonder if anyone can help me with a problem I am stuck on. I am using code that allows me to duplicate my workbook and then save it to another location. What I am trying to do is delete some of the modules from the duplicated workbook before saving.

I am using the below code and everything works apart from the module deleting part. Can anyone please help me with this please?

 'Create duplicate in temp folder.
    ThisWorkbook.SaveCopyAs strTempFolderPath & "2022a.xlsb"
    
    Application.Wait (Now + TimeValue("0:00:05"))
    '2) ----------> Open the duplicate file
    Set wbDuplicate = Workbooks.Open(strTempFolderPath & "2022a.xlsb")
  
   Dim vbCom As Object

   'Disabling the alert message
Application.DisplayAlerts = False
'Ignore errors
On Error Resume Next
'Delete the component
wb.VBProject.VBComponents.Remove wb.VBProject.VBComponents("test", "Module111", "Module3")
On Error GoTo 0
'Enabling the alert message
Application.DisplayAlerts = True

         On Error GoTo 0
    'Next
    
    '3) ----------> Close all tabs except the following:
     '- Import Core Data
    '- SendMail
    '- Test CP Weekly
    Worksheets("Lookup").Unprotect

    For Each shSheet In wbDuplicate.Sheets
        If shSheet.Name <> "TAB 1" And shSheet.Name <> "TAB2" And shSheet.Name <> "Tab3" Then
            shSheet.Visible = xlVeryHidden
            Sheets("Tab3").Visible = False

 

[Domenic]

Try the following ...

    Dim modulesToRemove As Variant
    Dim i As Long
    
    modulesToRemove = Array("test", "Module111", "Module3")
    
    On Error Resume Next
    For i = LBound(modulesToRemove) To UBound(modulesToRemove)
        With wb.VBProject.VBComponents
            .Remove .Item(modulesToRemove(i))
        End With
    Next i
    On Error GoTo 0

Hope this helps!

 

[StillUnderstanding]

Thanks @Domenic for your help with this.

I have changed the code to the below and the macro continues to run with no issue but it's not deleting the macros in the duplicated workbook.

I did have the macro code protected so I even took that off first to see if that was the issue and its not.

I'm totally as a loss on this.

 'Create duplicate in temp folder.
    ThisWorkbook.SaveCopyAs strTempFolderPath & "2022a.xlsb"
    
    Application.Wait (Now + TimeValue("0:00:05"))
    '2) ----------> Open the duplicate file
    Set wbDuplicate = Workbooks.Open(strTempFolderPath & "2022a.xlsb")
 
   Dim modulesToRemove As Variant
    Dim i As Long
    
    modulesToRemove = Array("test", "Module1", Macro2)
    
    On Error Resume Next
    For i = LBound(modulesToRemove) To UBound(modulesToRemove)
        With wb.VBProject.VBComponents
            .Remove .Item(modulesToRemove(i))
        End With
    Next i
    On Error GoTo 0
    
    '3) ----------> Close all tabs except the following:
     '- Import Core Data
    '- SendMail
    '- Test CP Weekly
    Worksheets("Lookup").Unprotect

    For Each shSheet In wbDuplicate.Sheets
        If shSheet.Name <> "TAB 1" And shSheet.Name <> "TAB2" And shSheet.Name <> "Tab3" Then
            shSheet.Visible = xlVeryHidden
            Sheets("Tab3").Visible = False

 

[Domenic]

First, I didn't notice it before, but I see that Macro2 is listed in the array. What is it? It's not a module name, is it? If so, it should be enclosed within quotes. Or is it a variable that contains the name of a module?

Secondly, comment out or temporary delete On Error Resume Next, and run the macro again. What happens this time? Do you get an error? If so, which one, and on which line?

 

[StillUnderstanding]

First, I didn't notice it before, but I see that Macro2 is listed in the array. What is it? It's not a module name, is it? If so, it should be enclosed within quotes. Or is it a variable that contains the name of a module?

Secondly, comment out or temporary delete On Error Resume Next, and run the macro again. What happens this time? Do you get an error? If so, which one, and on which line?

Sorry, that was my error with Macro2, should be "Marco2". That's the name of the module.

I commented out the Error Resume Next and it stopped with he following errer:- With wb.VBProject.VBComponents

 

[Domenic]

You didn't say which error message you're getting, but I'm assuming it's . . .

Rune time error '1004':

Programmatic access to Visual Basic Project is not trust

If so, you'll need to allow access to the Visual Basic Project, as follows . . .

On the Ribbon >> under the Developer tab >> in the Code group >> Macro Security >> select/click Trust access to the VBA project object model

Does this help?

 

[StillUnderstanding]

You didn't say which error message you're getting, but I'm assuming it's . . .

Rune time error '1004':

Programmatic access to Visual Basic Project is not trust

If so, you'll need to allow access to the Visual Basic Project, as follows . . .

On the Ribbon >> under the Developer tab >> in the Code group >> Macro Security >> select/click Trust access to the VBA project object model

Does this help?

Thanks @Domenic Oh, yes correct the error was 1004 so that helps me to the point that I now get the following error

Run-time error '438'

Object doesn't support the property or method

When I debug it brings me here:- With wbDuplicate.VBProject.VBComponents.VBProject.VBComponents

 

[Domenic]

With wbDuplicate.VBProject.VBComponents.VBProject.VBComponents

I don't know where you got that line from, but take another look at the code I provided you with, and you'll see the correct syntax . . .

 

[StillUnderstanding]

I don't know where you got that line from, but take another look at the code I provided you with, and you'll see the correct syntax . . .

Ok, got all the code as expected and corrected all of the above but I am now getting a

runtime error 424 
Object required

Not sure why but its just not running.

 

[StillUnderstanding]

I don't know where you got that line from, but take another look at the code I provided you with, and you'll see the correct syntax . . .

Ok, thank you I have it working. I removed the password from the VBA and it's working.

How do I run it so it disables the password in the project then re-enables it? Can this be done?