Removing Macro in ThisWorkbook

[Orion Cochrane]

I have a template that is an application that saves the template as a
pre-defined name format. It is also supposed to remove the Workbook_Open
macro from the newly saved workbook. Here is the code:

Private Sub Workbook_Open()
'Saves template with standard filename
Dim myPath As String, myFile As String, myExt As String
myPath = "Path"
myFile = "Constant " & Format(Date, "MMMM DD, YYYY")
myExt = ".xls"
If ActiveWorkbook.Name <> "Template.xls" Then _
ActiveWorkbook.VBProject.VBComponents.VBE.ActiveCodePane.CodeModule.DeleteLines 1, 11
If ActiveWorkbook.Name = "Template.xls" Then _
ActiveWorkbook.SaveAs Filename:=myPath & myFile & myExt
End Sub

Where it hangs up is where the workbook name <> "Template.xls". When I open
the new file and manually run the code (F5), it works. I get an "Object
Variable or With not defined" error. I tried adding a Call Workbook_Open line
before End Sub, and it still hung up. Is the active workbook still the
Template? How can I make sure that the active workbook is the newly saved one?

 

[NickH]

My understanding is that when you launch i.e. double-click on a
Template you create a 'New' copy of that template. The template itself
is not running so it appears that what you are trying to do is get the
code to delete itself while its running???

Perhaps you could take an alternative approach...

Presumably, the first time the 'New' workbook runs (at creation from
the template) is the only time you want the Workbook_Open macro to
run? This is the one time when the workbook is in the unique situation
of not having a path because it has not been saved. So - at the start
of procedure try something like...

Private Sub Workbook_Open()
If len(ThisWorkBook.Path) > 0 then Exit Sub
'
'
' your code here

btw the line *If ActiveWorkbook.Name = "Template.xls" Then _* strikes
me as odd. If you are truly talking about an Excel template called
"Template.XLT" I would expect you to be looking for an
ActiveWorkbook.Name like "Template1.xls" (or strictly "Template" & n &
".xls" where n increments by one each time the template is launched in
any given session - hmmm let's not get into that).

Anyway, I hope this helps.

NickH

 

[Orion Cochrane]

I can't save as an XLT because my network startup folder changes. The way I
want this to work is:

Open "Template.xls"
Save as predefined filename
With predefined name file
Remove Workbook_Open

I trusted myself to access VBA Project, and I am a trusted user. Can I
record the removal?

 

[NickH]

Hi Orion,

I don't believe you can write code that commits suicide and then saves
itself which is effectively what you are trying to do.

Another alternative then is to copy the Template's sheets to a new
workbook and then save that...

Private Sub Workbook_Open()
Dim wbk As Workbook
Dim i As Long

ThisWorkbook.Sheets(1).Copy
Set wbk = ActiveWorkbook

' Change n to the last sheet you want to copy
For i = 2 To n
ThisWorkbook.Sheets(i).Copy After:=wbk.Sheets(i - 1)
Next i

' Rest of your code here to save wbk etc.

End Sub

....this is a bit quick and dirty but hopefully gives you something to
build on.

Br,
NickH

 

[Orion Cochrane]

Thanks again. I'll just have to remove the code manually. I just like not
having to remember to save the file a certain way in a certain location.