Excel Macro with template activated in saved document

[grannyM]

I have an excel template that the Workbook - Open event has been set to call a macro attached to that workbook. After the macro runs, it saves the file in an .xls format. My problem is that when you open the saved file, the macro activates. I've been able to disable it for now by changing the Open event to:

If activeworkbook.Name = "ConvRecn.xls" then
     Call Convrecn
End if

is there a way to not have the vba code attached to the new file? Could there be something wrong with my save command? (See below)

ActiveWorkbook.SaveAs FileName:=savename$, FileFormat:=xlNormal, Password:="", _
        WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False

Thanks for any help

 

[Zathras]

Here is a macro that will remove the Workbook_Open event handler. If you only want to remove or replace individual lines, you can do that, too. You may have to add "Microsoft Visual Basic for Applications Extensibility" (VBEEXT1.OLB) to the References list in order to use it. (Tools/References...)

Option Explicit

Sub DeleteWorkbookOpenMacro()
Dim WorkbookCodeModule As CodeModule
  Set WorkbookCodeModule = ThisWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
  With WorkbookCodeModule
    If .Find("Private Sub Workbook_Open()", 0, 0, 999, 99) Then
      .DeleteLines _
            StartLine:=.ProcStartLine("Workbook_Open", vbext_pk_Proc), _
                Count:=.ProcCountLines("Workbook_Open", vbext_pk_Proc)
    End If
  End With
  Set WorkbookCodeModule = Nothing
End Sub