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?

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 *


"Claude" wrote in message
...
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?

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" wrote in message
...
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?

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...


-----Original Message-----
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 *


"Claude" wrote in
message
...
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?



.

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!!

-----Original Message-----
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" wrote in
message
...
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?



.