How to auto delete macro code after execution

We can also auto delete macro code written in excel workbook. Some times workbooks are generated thru the java/.net and other automation process, it can have macro codes or can have compatibility issues for further usage of spreadsheet/workbook. To remove those issues we have to delete macro codes. See below step we need to perform to achieve the same.

Lets edit macro code from previous post.

Sub Ato_Open()

    Dim wk As Worksheet
    ' Declare object variable
    Dim vbCom As Object
    Set vbCom = Application.VBE.ActiveVBProject.VBComponents
   
    MsgBox "It will Fill Values in cell of all sheets"
    For Each wk In ThisWorkbook.Worksheets
    wk.Activate
    With wk.Range("I5")
    .FormulaR1C1 = "test 1 2 3"
    End With
    Next wk

vbCom.Remove VBComponent:= _
vbCom.Item("Module1")
   
End Sub

* note - highlighed/Bold code  is newly added in previous post.

Now save the macro code close and reopen the workbook.

ERROR:
Run-time error '1004':

Programmatic access to Visual Basic Projects is not trusted

 

SOLUTION
Please look for below screen shot and follow the same process and enable the trust access to the VBA code

   

Close and reopen the workbook.. It works!!!!!!