bug with "CodeModule.DeleteLines"

[Claude]

Hi all

Can anybody tell me what's wrong with the following code?

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean,
Cancel As Boolean)

ActiveWorkbook.SaveCopyAs ("L:\test1.xls")
Application.EnableEvents = False
Workbooks.Open ("L:\test1.xls")

With Workbooks("test1.xls").VBProject.VBComponents
("ThisWorkbook").CodeModule
.DeleteLines 1, .CountOfLines
End With

Workbooks("test1.xls").Close savechanges:=True
ActiveWorkbook.SaveCopyAs ("L:\test2.xls")
Application.EnableEvents = True

End Sub

I would expect to get two files: test1.xls with the above
code deleted, test2.xls with the code in place. However, I
get two files with the code deleted!

Is there a logical explanation to this?

 

[Rob Bovey]

Hi Claude,

I just tested your code and it works for me exactly as expected. The
test1.xls workbook has no code behind the ThisWorkbook object while the
test2.xls workbook does. Are you sure you aren't doing anything else in your
program that might cause test2.xls to be overwritten or modified?

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

 

[Charles Williams]

I think you need to change the True to false as below

Workbooks("test1.xls").Close savechanges:=False


--
Charles
______________________
Decision Models
FastExcel 2.1 now available
www.DecisionModels.com

 

[Claude]

Hi Bob,
Thanks for the feedback, it's good to know that the code
is OK. Now to try and figure out what else could be
interfering...

 

[Guest]

Hi Charles,
Thanks for the reply. I want to first clone copies without
code, as a second step save the original file with the
code. However, on saving the code disappears for no
apparent reason!!