Hi everyone,

Programming in Excel is rather new to me. I’ve been able to get by thus far
by recording macros and looking at the code. I have been able to manipulate
the code by referring to the VBE help and these news groups.

I’m looking for assistance in modifying my code such that when the file is
saved (SAVE AS actually), all code is removed from the new file but not the
current file. I’ve searched these discussions groups and found a reference to
http://www.cpearson.com/excel/VBE.aspx where the following code will delete
all VBA in a project.

Sub DeleteAllVBACode()
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule

Set VBProj = ActiveWorkbook.VBProject

For Each VBComp In VBProj.VBComponents
If VBComp.Type = vbext_ct_Document Then
Set CodeMod = VBComp.CodeModule
With CodeMod
.DeleteLines 1, .CountOfLines
End With
Else
VBProj.VBComponents.Remove VBComp
End If
Next VBComp
End Sub

What I am not clear on is whether or not this code will delete all code in
the workbook that is currently open or whether it will delete the code in the
new workbook that is created when my code does a “save as�. The first line
under the declarations makes me think perhaps it deletes the code in the
workbook currently opened.

Am I correct?

FWIW, I’m including the code that executes when my user clicks the command
button to save the file. Sorry in advance if the lines don't wrap properly.

Private Sub SaveData_Click()

If Trim(Worksheets("Post-Service").Range("D3").Value = "") Then
MsgBox ("You must enter a serial number.")
Exit Sub
Else
Worksheets("Post-Service").Range("D3") =
UCase(Worksheets("Post-Service").Range("D3"))
If Left(Worksheets("Post-Service").Range("D3"), 1) = "C" Then
ActiveWorkbook.SaveCopyAs "\\MyPath\" & "SR50_SN_" & Range("d3")
& "_" & Format(Now, "yyyymmmdd") & ".xls"
Else
If MsgBox("Are you sure the serial number doesn't begin with
C?", vbYesNo) = vbYes Then
ActiveWorkbook.SaveCopyAs "\\MyPath\" & "SR50_SN_" & Range("d3")
& "_" & Format(Now, "yyyymmmdd") & ".xls"
Else
MsgBox ("Please fix the serial number.")
End If
End If
End If
End Sub

Thanks for any and all assistance.

Chris

This is the key line in Chip's code for determining which workbook it
deletes from.

Set VBProj = ActiveWorkbook.VBProject

Change ActiveWorkbook to the name of your new workbook that you want the
code deleted from, or just make sure that it is the active workbook. Either
way that ties it down to where you want to do the delete.




"CB" <(E-Mail Removed)> wrote in message
news:BC1474AD-E5B3-4765-837F-(E-Mail Removed)...
> Hi everyone,
>
> Programming in Excel is rather new to me. I've been able to get by thus
> far
> by recording macros and looking at the code. I have been able to
> manipulate
> the code by referring to the VBE help and these news groups.
>
> I'm looking for assistance in modifying my code such that when the file is
> saved (SAVE AS actually), all code is removed from the new file but not
> the
> current file. I've searched these discussions groups and found a reference
> to
> http://www.cpearson.com/excel/VBE.aspx where the following code will
> delete
> all VBA in a project.
>
> Sub DeleteAllVBACode()
> Dim VBProj As VBIDE.VBProject
> Dim VBComp As VBIDE.VBComponent
> Dim CodeMod As VBIDE.CodeModule
>
> Set VBProj = ActiveWorkbook.VBProject
>
> For Each VBComp In VBProj.VBComponents
> If VBComp.Type = vbext_ct_Document Then
> Set CodeMod = VBComp.CodeModule
> With CodeMod
> .DeleteLines 1, .CountOfLines
> End With
> Else
> VBProj.VBComponents.Remove VBComp
> End If
> Next VBComp
> End Sub
>
> What I am not clear on is whether or not this code will delete all code in
> the workbook that is currently open or whether it will delete the code in
> the
> new workbook that is created when my code does a "save as". The first line
> under the declarations makes me think perhaps it deletes the code in the
> workbook currently opened.
>
> Am I correct?
>
> FWIW, I'm including the code that executes when my user clicks the command
> button to save the file. Sorry in advance if the lines don't wrap
> properly.
>
> Private Sub SaveData_Click()
>
> If Trim(Worksheets("Post-Service").Range("D3").Value = "") Then
> MsgBox ("You must enter a serial number.")
> Exit Sub
> Else
> Worksheets("Post-Service").Range("D3") =
> UCase(Worksheets("Post-Service").Range("D3"))
> If Left(Worksheets("Post-Service").Range("D3"), 1) = "C" Then
> ActiveWorkbook.SaveCopyAs "\\MyPath\" & "SR50_SN_" &
> Range("d3")
> & "_" & Format(Now, "yyyymmmdd") & ".xls"
> Else
> If MsgBox("Are you sure the serial number doesn't begin with
> C?", vbYesNo) = vbYes Then
> ActiveWorkbook.SaveCopyAs "\\MyPath\" & "SR50_SN_" &
> Range("d3")
> & "_" & Format(Now, "yyyymmmdd") & ".xls"
> Else
> MsgBox ("Please fix the serial number.")
> End If
> End If
> End If
> End Sub
>
> Thanks for any and all assistance.
>
> Chris
>

Instead of creating a macro to delete the macros from the workbook. Instead
create a new workbook and copy the sheet to new book.

Sub CreateCopy

First = true
for each sht in thisworkbook
If First = True then
sht.copy 'create new workbook
set NewBk = Activeworkbook
First = False
else
with NewBk
sht.copy after:=.sheets(.sheets.count)
end with
end if
next sht
NewBk.SAveas filename:="book2.xls"
next sht
end sub


"CB" wrote:

> Hi everyone,
>
> Programming in Excel is rather new to me. I’ve been able to get by thus far
> by recording macros and looking at the code. I have been able to manipulate
> the code by referring to the VBE help and these news groups.
>
> I’m looking for assistance in modifying my code such that when the file is
> saved (SAVE AS actually), all code is removed from the new file but not the
> current file. I’ve searched these discussions groups and found a reference to
> http://www.cpearson.com/excel/VBE.aspx where the following code will delete
> all VBA in a project.
>
> Sub DeleteAllVBACode()
> Dim VBProj As VBIDE.VBProject
> Dim VBComp As VBIDE.VBComponent
> Dim CodeMod As VBIDE.CodeModule
>
> Set VBProj = ActiveWorkbook.VBProject
>
> For Each VBComp In VBProj.VBComponents
> If VBComp.Type = vbext_ct_Document Then
> Set CodeMod = VBComp.CodeModule
> With CodeMod
> .DeleteLines 1, .CountOfLines
> End With
> Else
> VBProj.VBComponents.Remove VBComp
> End If
> Next VBComp
> End Sub
>
> What I am not clear on is whether or not this code will delete all code in
> the workbook that is currently open or whether it will delete the code in the
> new workbook that is created when my code does a “save as�. The first line
> under the declarations makes me think perhaps it deletes the code in the
> workbook currently opened.
>
> Am I correct?
>
> FWIW, I’m including the code that executes when my user clicks the command
> button to save the file. Sorry in advance if the lines don't wrap properly.
>
> Private Sub SaveData_Click()
>
> If Trim(Worksheets("Post-Service").Range("D3").Value = "") Then
> MsgBox ("You must enter a serial number.")
> Exit Sub
> Else
> Worksheets("Post-Service").Range("D3") =
> UCase(Worksheets("Post-Service").Range("D3"))
> If Left(Worksheets("Post-Service").Range("D3"), 1) = "C" Then
> ActiveWorkbook.SaveCopyAs "\\MyPath\" & "SR50_SN_" & Range("d3")
> & "_" & Format(Now, "yyyymmmdd") & ".xls"
> Else
> If MsgBox("Are you sure the serial number doesn't begin with
> C?", vbYesNo) = vbYes Then
> ActiveWorkbook.SaveCopyAs "\\MyPath\" & "SR50_SN_" & Range("d3")
> & "_" & Format(Now, "yyyymmmdd") & ".xls"
> Else
> MsgBox ("Please fix the serial number.")
> End If
> End If
> End If
> End Sub
>
> Thanks for any and all assistance.
>
> Chris
>

On Mar 19, 12:10*pm, CB <C...@discussions.microsoft.com> wrote:
> Hi everyone,
>
> Programming in Excel is rather new to me. I’ve been able to get by thusfar
> by recording macros and looking at the code. I have been able to manipulate
> the code by referring to the VBE help and these news groups.
>
> I’m looking for assistance in modifying my code such that when the fileis
> saved (SAVE AS actually), all code is removed from the new file but not the
> current file. I’ve searched these discussions groups and found a reference tohttp://www.cpearson.com/excel/VBE.aspxwhere the following code will delete
> all VBA in a project.
>
> Sub DeleteAllVBACode()
> * * * * Dim VBProj As VBIDE.VBProject
> * * * * Dim VBComp As VBIDE.VBComponent
> * * * * Dim CodeMod As VBIDE.CodeModule
>
> * * * * Set VBProj = ActiveWorkbook.VBProject
>
> * * * * For Each VBComp In VBProj.VBComponents
> * * * * * * If VBComp.Type = vbext_ct_Document Then
> * * * * * * * * Set CodeMod = VBComp.CodeModule
> * * * * * * * * With CodeMod
> * * * * * * * * * * .DeleteLines 1, .CountOfLines
> * * * * * * * * End With
> * * * * * * Else
> * * * * * * * * VBProj.VBComponents.Remove VBComp
> * * * * * * End If
> * * * * Next VBComp
> * * End Sub
>
> What I am not clear on is whether or not this code will delete all code in
> the workbook that is currently open or whether it will delete the code inthe
> new workbook that is created when my code does a “save as”. The firstline
> under the declarations makes me think perhaps it deletes the code in the
> workbook currently opened.
>
> Am I correct?
>
> FWIW, I’m including the code that executes when my user clicks the command
> button to save the file. Sorry in advance if the lines don't wrap properly.
>
> Private Sub SaveData_Click()
>
> * * If Trim(Worksheets("Post-Service").Range("D3").Value = "") Then
> * * * * MsgBox ("You must enter a serial number.")
> * * Exit Sub
> * * Else
> * * * * Worksheets("Post-Service").Range("D3") =
> UCase(Worksheets("Post-Service").Range("D3"))
> * * * * If Left(Worksheets("Post-Service").Range("D3"), 1) = "C" Then
> * * * * * * ActiveWorkbook.SaveCopyAs "\\MyPath\" & "SR50_SN_" & Range("d3")
> & "_" & Format(Now, "yyyymmmdd") & ".xls"
> * * * * Else
> * * * * * * If MsgBox("Are you sure the serial number doesn'tbegin with
> C?", vbYesNo) = vbYes Then
> * * * * * * ActiveWorkbook.SaveCopyAs "\\MyPath\" & "SR50_SN_" & Range("d3")
> & "_" & Format(Now, "yyyymmmdd") & ".xls"
> * * * * Else
> * * * * * * MsgBox ("Please fix the serial number.")
> * * * * End If
> * * End If
> * * End If
> End Sub
>
> Thanks for any and all assistance.
>
> Chris

Chris,

In Chip's code, VBProj is an object created to reference the
VBProjects in the ActiveWorkbook. VBComp includes the components of
VBProj (or the VBProjects in the ActiveWorkbook). In your code, you
are using ActiveWorkbook, so whatever workbook is currently selected
will be the ActiveWorkbook. There are a few other ways to refer to a
Workbook. You can use Me, ThisWorkbook, an index number, or a
workbook name. The code will execute in the workbook you specify.

Let me know if this helps.

Best,

Matt Herbert

Hi Joel,

Thanks for responding. I thought I would try this. First, the debugger
didn't like the fact that you had "next sht" twice. Once I deleted the last
instance, compiled, then ran the code, I received the following error...

Run-time Error "438"
Object doesn't support this property or method.

The debugger then stops on the line:

For each sht in This Workbook.

Thanks again.

Chris

"Joel" wrote:

> Instead of creating a macro to delete the macros from the workbook. Instead
> create a new workbook and copy the sheet to new book.
>
> Sub CreateCopy
>
> First = true
> for each sht in thisworkbook
> If First = True then
> sht.copy 'create new workbook
> set NewBk = Activeworkbook
> First = False
> else
> with NewBk
> sht.copy after:=.sheets(.sheets.count)
> end with
> end if
> next sht
> NewBk.SAveas filename:="book2.xls"
> next sht
> end sub
>
>
> "CB" wrote:
>
> > Hi everyone,
> >
> > Programming in Excel is rather new to me. I’ve been able to get by thus far
> > by recording macros and looking at the code. I have been able to manipulate
> > the code by referring to the VBE help and these news groups.
> >
> > I’m looking for assistance in modifying my code such that when the file is
> > saved (SAVE AS actually), all code is removed from the new file but not the
> > current file. I’ve searched these discussions groups and found a reference to
> > http://www.cpearson.com/excel/VBE.aspx where the following code will delete
> > all VBA in a project.
> >
> > Sub DeleteAllVBACode()
> > Dim VBProj As VBIDE.VBProject
> > Dim VBComp As VBIDE.VBComponent
> > Dim CodeMod As VBIDE.CodeModule
> >
> > Set VBProj = ActiveWorkbook.VBProject
> >
> > For Each VBComp In VBProj.VBComponents
> > If VBComp.Type = vbext_ct_Document Then
> > Set CodeMod = VBComp.CodeModule
> > With CodeMod
> > .DeleteLines 1, .CountOfLines
> > End With
> > Else
> > VBProj.VBComponents.Remove VBComp
> > End If
> > Next VBComp
> > End Sub
> >
> > What I am not clear on is whether or not this code will delete all code in
> > the workbook that is currently open or whether it will delete the code in the
> > new workbook that is created when my code does a “save as�. The first line
> > under the declarations makes me think perhaps it deletes the code in the
> > workbook currently opened.
> >
> > Am I correct?
> >
> > FWIW, I’m including the code that executes when my user clicks the command
> > button to save the file. Sorry in advance if the lines don't wrap properly.
> >
> > Private Sub SaveData_Click()
> >
> > If Trim(Worksheets("Post-Service").Range("D3").Value = "") Then
> > MsgBox ("You must enter a serial number.")
> > Exit Sub
> > Else
> > Worksheets("Post-Service").Range("D3") =
> > UCase(Worksheets("Post-Service").Range("D3"))
> > If Left(Worksheets("Post-Service").Range("D3"), 1) = "C" Then
> > ActiveWorkbook.SaveCopyAs "\\MyPath\" & "SR50_SN_" & Range("d3")
> > & "_" & Format(Now, "yyyymmmdd") & ".xls"
> > Else
> > If MsgBox("Are you sure the serial number doesn't begin with
> > C?", vbYesNo) = vbYes Then
> > ActiveWorkbook.SaveCopyAs "\\MyPath\" & "SR50_SN_" & Range("d3")
> > & "_" & Format(Now, "yyyymmmdd") & ".xls"
> > Else
> > MsgBox ("Please fix the serial number.")
> > End If
> > End If
> > End If
> > End Sub
> >
> > Thanks for any and all assistance.
> >
> > Chris
> >

Hi there,

Thanks for taking the time to respond. I thought as much.

To change ActiveWorkbook to the name of my workbook, I presume I'll have to
specify the path as well, since the workbook will be saved over the network.
I'm not clear on what the proper syntax would be. Could you help?

Hmm, since I'm naming the file based on the value in cell D3 and date, I'm
thinking this complicates things?

Regards,
Chris

"JLGWhiz" wrote:

> This is the key line in Chip's code for determining which workbook it
> deletes from.
>
> Set VBProj = ActiveWorkbook.VBProject
>
> Change ActiveWorkbook to the name of your new workbook that you want the
> code deleted from, or just make sure that it is the active workbook. Either
> way that ties it down to where you want to do the delete.
>
>
>
>
> "CB" <(E-Mail Removed)> wrote in message
> news:BC1474AD-E5B3-4765-837F-(E-Mail Removed)...
> > Hi everyone,
> >
> > Programming in Excel is rather new to me. I've been able to get by thus
> > far
> > by recording macros and looking at the code. I have been able to
> > manipulate
> > the code by referring to the VBE help and these news groups.
> >
> > I'm looking for assistance in modifying my code such that when the file is
> > saved (SAVE AS actually), all code is removed from the new file but not
> > the
> > current file. I've searched these discussions groups and found a reference
> > to
> > http://www.cpearson.com/excel/VBE.aspx where the following code will
> > delete
> > all VBA in a project.
> >
> > Sub DeleteAllVBACode()
> > Dim VBProj As VBIDE.VBProject
> > Dim VBComp As VBIDE.VBComponent
> > Dim CodeMod As VBIDE.CodeModule
> >
> > Set VBProj = ActiveWorkbook.VBProject
> >
> > For Each VBComp In VBProj.VBComponents
> > If VBComp.Type = vbext_ct_Document Then
> > Set CodeMod = VBComp.CodeModule
> > With CodeMod
> > .DeleteLines 1, .CountOfLines
> > End With
> > Else
> > VBProj.VBComponents.Remove VBComp
> > End If
> > Next VBComp
> > End Sub
> >
> > What I am not clear on is whether or not this code will delete all code in
> > the workbook that is currently open or whether it will delete the code in
> > the
> > new workbook that is created when my code does a "save as". The first line
> > under the declarations makes me think perhaps it deletes the code in the
> > workbook currently opened.
> >
> > Am I correct?
> >
> > FWIW, I'm including the code that executes when my user clicks the command
> > button to save the file. Sorry in advance if the lines don't wrap
> > properly.
> >
> > Private Sub SaveData_Click()
> >
> > If Trim(Worksheets("Post-Service").Range("D3").Value = "") Then
> > MsgBox ("You must enter a serial number.")
> > Exit Sub
> > Else
> > Worksheets("Post-Service").Range("D3") =
> > UCase(Worksheets("Post-Service").Range("D3"))
> > If Left(Worksheets("Post-Service").Range("D3"), 1) = "C" Then
> > ActiveWorkbook.SaveCopyAs "\\MyPath\" & "SR50_SN_" &
> > Range("d3")
> > & "_" & Format(Now, "yyyymmmdd") & ".xls"
> > Else
> > If MsgBox("Are you sure the serial number doesn't begin with
> > C?", vbYesNo) = vbYes Then
> > ActiveWorkbook.SaveCopyAs "\\MyPath\" & "SR50_SN_" &
> > Range("d3")
> > & "_" & Format(Now, "yyyymmmdd") & ".xls"
> > Else
> > MsgBox ("Please fix the serial number.")
> > End If
> > End If
> > End If
> > End Sub
> >
> > Thanks for any and all assistance.
> >
> > Chris
> >
>
>
>

Hi Matt,

Thanks for replying. As I mentioned in my reply to JLGWhiz, I'm not sure of
the proper syntax to refer to the file I'm saving (not the currently opened
on). Your thoughts?

Regards,

Chris

"(E-Mail Removed)" wrote:

> On Mar 19, 12:10 pm, CB <C...@discussions.microsoft.com> wrote:
> > Hi everyone,
> >
> > Programming in Excel is rather new to me. I’ve been able to get by thus far
> > by recording macros and looking at the code. I have been able to manipulate
> > the code by referring to the VBE help and these news groups.
> >
> > I’m looking for assistance in modifying my code such that when the file is
> > saved (SAVE AS actually), all code is removed from the new file but not the
> > current file. I’ve searched these discussions groups and found a reference tohttp://www.cpearson.com/excel/VBE.aspxwhere the following code will delete
> > all VBA in a project.
> >
> > Sub DeleteAllVBACode()
> > Dim VBProj As VBIDE.VBProject
> > Dim VBComp As VBIDE.VBComponent
> > Dim CodeMod As VBIDE.CodeModule
> >
> > Set VBProj = ActiveWorkbook.VBProject
> >
> > For Each VBComp In VBProj.VBComponents
> > If VBComp.Type = vbext_ct_Document Then
> > Set CodeMod = VBComp.CodeModule
> > With CodeMod
> > .DeleteLines 1, .CountOfLines
> > End With
> > Else
> > VBProj.VBComponents.Remove VBComp
> > End If
> > Next VBComp
> > End Sub
> >
> > What I am not clear on is whether or not this code will delete all code in
> > the workbook that is currently open or whether it will delete the code in the
> > new workbook that is created when my code does a “save as�. The first line
> > under the declarations makes me think perhaps it deletes the code in the
> > workbook currently opened.
> >
> > Am I correct?
> >
> > FWIW, I’m including the code that executes when my user clicks the command
> > button to save the file. Sorry in advance if the lines don't wrap properly.
> >
> > Private Sub SaveData_Click()
> >
> > If Trim(Worksheets("Post-Service").Range("D3").Value = "") Then
> > MsgBox ("You must enter a serial number.")
> > Exit Sub
> > Else
> > Worksheets("Post-Service").Range("D3") =
> > UCase(Worksheets("Post-Service").Range("D3"))
> > If Left(Worksheets("Post-Service").Range("D3"), 1) = "C" Then
> > ActiveWorkbook.SaveCopyAs "\\MyPath\" & "SR50_SN_" & Range("d3")
> > & "_" & Format(Now, "yyyymmmdd") & ".xls"
> > Else
> > If MsgBox("Are you sure the serial number doesn't begin with
> > C?", vbYesNo) = vbYes Then
> > ActiveWorkbook.SaveCopyAs "\\MyPath\" & "SR50_SN_" & Range("d3")
> > & "_" & Format(Now, "yyyymmmdd") & ".xls"
> > Else
> > MsgBox ("Please fix the serial number.")
> > End If
> > End If
> > End If
> > End Sub
> >
> > Thanks for any and all assistance.
> >
> > Chris
>
> Chris,
>
> In Chip's code, VBProj is an object created to reference the
> VBProjects in the ActiveWorkbook. VBComp includes the components of
> VBProj (or the VBProjects in the ActiveWorkbook). In your code, you
> are using ActiveWorkbook, so whatever workbook is currently selected
> will be the ActiveWorkbook. There are a few other ways to refer to a
> Workbook. You can use Me, ThisWorkbook, an index number, or a
> workbook name. The code will execute in the workbook you specify.
>
> Let me know if this helps.
>
> Best,
>
> Matt Herbert
>

If the line is as you show it, then remove the space from between This and
Workbook... the correct reference is ThisWorkbook

--
Rick (MVP - Excel)


"CB" <(E-Mail Removed)> wrote in message
news:0C847BD9-6387-4887-B0D2-(E-Mail Removed)...
> Hi Joel,
>
> Thanks for responding. I thought I would try this. First, the debugger
> didn't like the fact that you had "next sht" twice. Once I deleted the
> last
> instance, compiled, then ran the code, I received the following error...
>
> Run-time Error "438"
> Object doesn't support this property or method.
>
> The debugger then stops on the line:
>
> For each sht in This Workbook.
>
> Thanks again.
>
> Chris
>
> "Joel" wrote:
>
>> Instead of creating a macro to delete the macros from the workbook.
>> Instead
>> create a new workbook and copy the sheet to new book.
>>
>> Sub CreateCopy
>>
>> First = true
>> for each sht in thisworkbook
>> If First = True then
>> sht.copy 'create new workbook
>> set NewBk = Activeworkbook
>> First = False
>> else
>> with NewBk
>> sht.copy after:=.sheets(.sheets.count)
>> end with
>> end if
>> next sht
>> NewBk.SAveas filename:="book2.xls"
>> next sht
>> end sub
>>
>>
>> "CB" wrote:
>>
>> > Hi everyone,
>> >
>> > Programming in Excel is rather new to me. I’ve been able to get by thus
>> > far
>> > by recording macros and looking at the code. I have been able to
>> > manipulate
>> > the code by referring to the VBE help and these news groups.
>> >
>> > I’m looking for assistance in modifying my code such that when the file
>> > is
>> > saved (SAVE AS actually), all code is removed from the new file but not
>> > the
>> > current file. I’ve searched these discussions groups and found a
>> > reference to
>> > http://www.cpearson.com/excel/VBE.aspx where the following code will
>> > delete
>> > all VBA in a project.
>> >
>> > Sub DeleteAllVBACode()
>> > Dim VBProj As VBIDE.VBProject
>> > Dim VBComp As VBIDE.VBComponent
>> > Dim CodeMod As VBIDE.CodeModule
>> >
>> > Set VBProj = ActiveWorkbook.VBProject
>> >
>> > For Each VBComp In VBProj.VBComponents
>> > If VBComp.Type = vbext_ct_Document Then
>> > Set CodeMod = VBComp.CodeModule
>> > With CodeMod
>> > .DeleteLines 1, .CountOfLines
>> > End With
>> > Else
>> > VBProj.VBComponents.Remove VBComp
>> > End If
>> > Next VBComp
>> > End Sub
>> >
>> > What I am not clear on is whether or not this code will delete all code
>> > in
>> > the workbook that is currently open or whether it will delete the code
>> > in the
>> > new workbook that is created when my code does a “save as�. The first
>> > line
>> > under the declarations makes me think perhaps it deletes the code in
>> > the
>> > workbook currently opened.
>> >
>> > Am I correct?
>> >
>> > FWIW, I’m including the code that executes when my user clicks the
>> > command
>> > button to save the file. Sorry in advance if the lines don't wrap
>> > properly.
>> >
>> > Private Sub SaveData_Click()
>> >
>> > If Trim(Worksheets("Post-Service").Range("D3").Value = "") Then
>> > MsgBox ("You must enter a serial number.")
>> > Exit Sub
>> > Else
>> > Worksheets("Post-Service").Range("D3") =
>> > UCase(Worksheets("Post-Service").Range("D3"))
>> > If Left(Worksheets("Post-Service").Range("D3"), 1) = "C" Then
>> > ActiveWorkbook.SaveCopyAs "\\MyPath\" & "SR50_SN_" &
>> > Range("d3")
>> > & "_" & Format(Now, "yyyymmmdd") & ".xls"
>> > Else
>> > If MsgBox("Are you sure the serial number doesn't begin
>> > with
>> > C?", vbYesNo) = vbYes Then
>> > ActiveWorkbook.SaveCopyAs "\\MyPath\" & "SR50_SN_" &
>> > Range("d3")
>> > & "_" & Format(Now, "yyyymmmdd") & ".xls"
>> > Else
>> > MsgBox ("Please fix the serial number.")
>> > End If
>> > End If
>> > End If
>> > End Sub
>> >
>> > Thanks for any and all assistance.
>> >
>> > Chris
>> >

As others have pointed out, the code as written works on the
ActiveWorkbook, specified only on the line

Set VBProj = ActiveWorkbook.VBProject

As I see it, you have two workbooks under consideration:

Original.xls
' and
SavedAs.xls

When you do the Save As operation, Original.xls is closed and
SavedAs.xls is open and the Active Workbook. If you want to remove
all the VBA code from the SavedAs.xls workbook, you can run the code
as is. However, if you need to run the code against Original.xls (or
any other closed workbook), you will need to open that workbook. The
code works only on open workbooks. You can specify the workbook whose
code is to be stripped with something like

Set VBProj = Workbooks("WorkbookName.xls")

If you need to open the workbook, you can use code like

Dim WB As Workbook
Set WB = Workbooks.Open("C:\Book1.xls")
Set VBProj = WB.VBProject

The variable WB is set to the instance of the opened C:\Book1.xls
workbook and the VBProj variable is set to the VBProject of that
workbook. All the rest of the code will refer to that workbook.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Thu, 19 Mar 2009 10:10:01 -0700, CB <(E-Mail Removed)>
wrote:

>Hi everyone,
>
>Programming in Excel is rather new to me. I’ve been able to get by thus far
>by recording macros and looking at the code. I have been able to manipulate
>the code by referring to the VBE help and these news groups.
>
>I’m looking for assistance in modifying my code such that when the file is
>saved (SAVE AS actually), all code is removed from the new file but not the
>current file. I’ve searched these discussions groups and found a reference to
>http://www.cpearson.com/excel/VBE.aspx where the following code will delete
>all VBA in a project.
>
>Sub DeleteAllVBACode()
> Dim VBProj As VBIDE.VBProject
> Dim VBComp As VBIDE.VBComponent
> Dim CodeMod As VBIDE.CodeModule
>
> Set VBProj = ActiveWorkbook.VBProject
>
> For Each VBComp In VBProj.VBComponents
> If VBComp.Type = vbext_ct_Document Then
> Set CodeMod = VBComp.CodeModule
> With CodeMod
> .DeleteLines 1, .CountOfLines
> End With
> Else
> VBProj.VBComponents.Remove VBComp
> End If
> Next VBComp
> End Sub
>
>What I am not clear on is whether or not this code will delete all code in
>the workbook that is currently open or whether it will delete the code in the
>new workbook that is created when my code does a “save as”. The first line
>under the declarations makes me think perhaps it deletes the code in the
>workbook currently opened.
>
>Am I correct?
>
>FWIW, I’m including the code that executes when my user clicks the command
>button to save the file. Sorry in advance if the lines don't wrap properly.
>
>Private Sub SaveData_Click()
>
> If Trim(Worksheets("Post-Service").Range("D3").Value = "") Then
> MsgBox ("You must enter a serial number.")
> Exit Sub
> Else
> Worksheets("Post-Service").Range("D3") =
>UCase(Worksheets("Post-Service").Range("D3"))
> If Left(Worksheets("Post-Service").Range("D3"), 1) = "C" Then
> ActiveWorkbook.SaveCopyAs "\\MyPath\" & "SR50_SN_" & Range("d3")
>& "_" & Format(Now, "yyyymmmdd") & ".xls"
> Else
> If MsgBox("Are you sure the serial number doesn't begin with
>C?", vbYesNo) = vbYes Then
> ActiveWorkbook.SaveCopyAs "\\MyPath\" & "SR50_SN_" & Range("d3")
>& "_" & Format(Now, "yyyymmmdd") & ".xls"
> Else
> MsgBox ("Please fix the serial number.")
> End If
> End If
> End If
>End Sub
>
>Thanks for any and all assistance.
>
>Chris

Hi Chip,

Thanks for responding. I was perplexed by your statement...

"When you do the Save As operation, Original.xls is closed and SavedAs.xls
is open and the Active Workbook. "

This is not the behaviour I see. When I click the button to save the file, a
new file is saved in the background (in that I don't see it open at all) that
contains all the data that was entered and my original file remains open. Of
course, the original files also contains the data but I have a button that
clears the data so the tech can run another test.

Chris

"Chip Pearson" wrote:

>
> As others have pointed out, the code as written works on the
> ActiveWorkbook, specified only on the line
>
> Set VBProj = ActiveWorkbook.VBProject
>
> As I see it, you have two workbooks under consideration:
>
> Original.xls
> ' and
> SavedAs.xls
>
> When you do the Save As operation, Original.xls is closed and
> SavedAs.xls is open and the Active Workbook. If you want to remove
> all the VBA code from the SavedAs.xls workbook, you can run the code
> as is. However, if you need to run the code against Original.xls (or
> any other closed workbook), you will need to open that workbook. The
> code works only on open workbooks. You can specify the workbook whose
> code is to be stripped with something like
>
> Set VBProj = Workbooks("WorkbookName.xls")
>
> If you need to open the workbook, you can use code like
>
> Dim WB As Workbook
> Set WB = Workbooks.Open("C:\Book1.xls")
> Set VBProj = WB.VBProject
>
> The variable WB is set to the instance of the opened C:\Book1.xls
> workbook and the VBProj variable is set to the VBProject of that
> workbook. All the rest of the code will refer to that workbook.
>
> Cordially,
> Chip Pearson
> Microsoft Most Valuable Professional
> Excel Product Group, 1998 - 2009
> Pearson Software Consulting, LLC
> www.cpearson.com
> (email on web site)
>
>
> On Thu, 19 Mar 2009 10:10:01 -0700, CB <(E-Mail Removed)>
> wrote:
>
> >Hi everyone,
> >
> >Programming in Excel is rather new to me. I’ve been able to get by thus far
> >by recording macros and looking at the code. I have been able to manipulate
> >the code by referring to the VBE help and these news groups.
> >
> >I’m looking for assistance in modifying my code such that when the file is
> >saved (SAVE AS actually), all code is removed from the new file but not the
> >current file. I’ve searched these discussions groups and found a reference to
> >http://www.cpearson.com/excel/VBE.aspx where the following code will delete
> >all VBA in a project.
> >
> >Sub DeleteAllVBACode()
> > Dim VBProj As VBIDE.VBProject
> > Dim VBComp As VBIDE.VBComponent
> > Dim CodeMod As VBIDE.CodeModule
> >
> > Set VBProj = ActiveWorkbook.VBProject
> >
> > For Each VBComp In VBProj.VBComponents
> > If VBComp.Type = vbext_ct_Document Then
> > Set CodeMod = VBComp.CodeModule
> > With CodeMod
> > .DeleteLines 1, .CountOfLines
> > End With
> > Else
> > VBProj.VBComponents.Remove VBComp
> > End If
> > Next VBComp
> > End Sub
> >
> >What I am not clear on is whether or not this code will delete all code in
> >the workbook that is currently open or whether it will delete the code in the
> >new workbook that is created when my code does a “save as�. The first line
> >under the declarations makes me think perhaps it deletes the code in the
> >workbook currently opened.
> >
> >Am I correct?
> >
> >FWIW, I’m including the code that executes when my user clicks the command
> >button to save the file. Sorry in advance if the lines don't wrap properly.
> >
> >Private Sub SaveData_Click()
> >
> > If Trim(Worksheets("Post-Service").Range("D3").Value = "") Then
> > MsgBox ("You must enter a serial number.")
> > Exit Sub
> > Else
> > Worksheets("Post-Service").Range("D3") =
> >UCase(Worksheets("Post-Service").Range("D3"))
> > If Left(Worksheets("Post-Service").Range("D3"), 1) = "C" Then
> > ActiveWorkbook.SaveCopyAs "\\MyPath\" & "SR50_SN_" & Range("d3")
> >& "_" & Format(Now, "yyyymmmdd") & ".xls"
> > Else
> > If MsgBox("Are you sure the serial number doesn't begin with
> >C?", vbYesNo) = vbYes Then
> > ActiveWorkbook.SaveCopyAs "\\MyPath\" & "SR50_SN_" & Range("d3")
> >& "_" & Format(Now, "yyyymmmdd") & ".xls"
> > Else
> > MsgBox ("Please fix the serial number.")
> > End If
> > End If
> > End If
> >End Sub
> >
> >Thanks for any and all assistance.
> >
> >Chris
>