Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing Macro in ThisWorkbook
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.ActiveCo dePane.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? -- I am running on Excel 2003, unless otherwise stated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing Macro in ThisWorkbook
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing Macro in ThisWorkbook
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? -- I am running on Excel 2003, unless otherwise stated. "NickH" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing Macro in ThisWorkbook
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing Macro in ThisWorkbook
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. -- I am running on Excel 2003, unless otherwise stated. "NickH" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Personal.xls macro that writes to ThisWorkBook | Excel Programming | |||
Macro to add code to ThisWorkbook module | Excel Programming | |||
'ThisWorkbook' Macro Question | Excel Programming | |||
Module1 vs Thisworkbook for Macro | Excel Programming |