Hi to the experts in excel programming.

What is the commands or script for deleting a macro automatically using
another workbook macro.

Hi Ddiicc,

See Chip Pearson VBE programming page at:

http://www.cpearson.com/excel/vbe.htm

See particularly the section entitled: Deleting A Procedure From A Module


---
Regards,
Norman



"ddiicc" wrote in message
...
Hi to the experts in excel programming.

What is the commands or script for deleting a macro automatically using
another workbook macro.

Hi Norman,

What If I want to delete a module in another workbook 2 using the module in
workbook 1... so how should go about writing this in workbook module???



"Norman Jones" wrote:

Hi Ddiicc,

See Chip Pearson VBE programming page at:

http://www.cpearson.com/excel/vbe.htm

See particularly the section entitled: Deleting A Procedure From A Module


---
Regards,
Norman



"ddiicc" wrote in message
...
Hi to the experts in excel programming.

What is the commands or script for deleting a macro automatically using
another workbook macro.

Hi Ddiicc,

Try:

Sub DeleteModule()
Dim VBComp As VBComponent
Set VBComp = Workbooks("Book2.xls").VBProject. _
VBComponents("NewModule")
Workbooks("Book2.xls").VBProject.VBComponents.Remo ve VBComp
End Sub


---
Regards,
Norman



"ddiicc" wrote in message
...
Hi Norman,

What If I want to delete a module in another workbook 2 using the module
in
workbook 1... so how should go about writing this in workbook module???



"Norman Jones" wrote:

Hi Ddiicc,

See Chip Pearson VBE programming page at:

http://www.cpearson.com/excel/vbe.htm

See particularly the section entitled: Deleting A Procedure From A Module


---
Regards,
Norman



"ddiicc" wrote in message
...
Hi to the experts in excel programming.

What is the commands or script for deleting a macro automatically using
another workbook macro.

Hi Norman,
Thanks for responding, I am rather a beginner in macro writing,
So I would like to ask on how to add in the code that you given.
Eg..My ending of the macros is stated below.

ActiveWorkbook.SaveAs Filename:="E:\Jong\Python A HSA\HSA Yield For All
Lines 2005\C LGC\All HSA LGC Stations Yield\G Jul 2005 LGC Yield\HSA LGC01
All Model 1st Pass Yield Jul 2005.xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False, CreateBackup:=False

End Sub


So how do I add in the script you have given, I have about 11 modules to
delete
after the workbook is saved as stated on the above...

Regards ddiicc

"Norman Jones" wrote:

Hi Ddiicc,

Try:

Sub DeleteModule()
Dim VBComp As VBComponent
Set VBComp = Workbooks("Book2.xls").VBProject. _
VBComponents("NewModule")
Workbooks("Book2.xls").VBProject.VBComponents.Remo ve VBComp
End Sub


---
Regards,
Norman



"ddiicc" wrote in message
...
Hi Norman,

What If I want to delete a module in another workbook 2 using the module
in
workbook 1... so how should go about writing this in workbook module???



"Norman Jones" wrote:

Hi Ddiicc,

See Chip Pearson VBE programming page at:

http://www.cpearson.com/excel/vbe.htm

See particularly the section entitled: Deleting A Procedure From A Module


---
Regards,
Norman



"ddiicc" wrote in message
...
Hi to the experts in excel programming.

What is the commands or script for deleting a macro automatically using
another workbook macro.

Hi Ddiicc,

On the link page I gave you, Chip Pearson gives a DeleteAllVBA procedure
which 'does what it says on the label'.

Customising for your specific workbook, try:

Sub DeleteAllVBA()

Dim VBComp As VBIDE.VBComponent
Dim VBComps As VBIDE.VBComponents
Dim WB As Workbook

Set WB = Workbooks("HSA LGC01.xls")

Set VBComps = WB.VBProject.VBComponents

For Each VBComp In VBComps
Select Case VBComp.Type
Case vbext_ct_StdModule, vbext_ct_MSForm, _
vbext_ct_ClassModule
VBComps.Remove VBComp
Case Else
With VBComp.CodeModule
.DeleteLines 1, .CountOfLines
End With
End Select
Next VBComp

End Sub

'=====================================
Before using this code, thank Chip and carefully read the warning he
provided for the procedu
The procedure below will delete all the VBA code in a project. You should
use this procedure with care, as it will permanently delete the code.
Standard modules, user forms, and class modules will be removed, and code
within the ThisWorkbook module and the sheet modules will be deleted. You
may want to export the VBA code, using the procedure above, before deleting
the VBA code.
'<<=====================================

---
Regards,
Norman



"ddiicc" wrote in message
...
Hi Norman,
Thanks for responding, I am rather a beginner in macro writing,
So I would like to ask on how to add in the code that you given.
Eg..My ending of the macros is stated below.

ActiveWorkbook.SaveAs Filename:="E:\Jong\Python A HSA\HSA Yield For All
Lines 2005\C LGC\All HSA LGC Stations Yield\G Jul 2005 LGC Yield\HSA LGC01
All Model 1st Pass Yield Jul 2005.xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False, CreateBackup:=False

End Sub


So how do I add in the script you have given, I have about 11 modules to
delete
after the workbook is saved as stated on the above...

Regards ddiicc

"Norman Jones" wrote:

Hi Ddiicc,

Try:

Sub DeleteModule()
Dim VBComp As VBComponent
Set VBComp = Workbooks("Book2.xls").VBProject. _

VBComponents("NewModule")
Workbooks("Book2.xls").VBProject.VBComponents.Remo ve VBComp
End Sub


---
Regards,
Norman



"ddiicc" wrote in message
...
Hi Norman,

What If I want to delete a module in another workbook 2 using the
module
in
workbook 1... so how should go about writing this in workbook module???



"Norman Jones" wrote:

Hi Ddiicc,

See Chip Pearson VBE programming page at:

http://www.cpearson.com/excel/vbe.htm

See particularly the section entitled: Deleting A Procedure From A
Module


---
Regards,
Norman



"ddiicc" wrote in message
...
Hi to the experts in excel programming.

What is the commands or script for deleting a macro automatically
using
another workbook macro.

Hi Norman,
I have try and try but still I receive some errors.

I will stated my examples clearly, so you could help me or give me the words
to fill in the blanks.

"Workbook A" name is "HSA template 1"
"Workbook B" name is "HSA template 2"

Workbook A contains a module name "AllDataYieldCrunch"
Workbook B conains a 2 module name, 1st is "SortData", 2nd is "CrunchData"

So in my workbook A, I want to add the code, macro or scripts, inside my
module "AllDataYieldCrunch" together. Then it will also delete the 2 modules
in
workbook B.

Workbook A module stated below :-

Sub ########()

####$##^^%&^
#$#%^$^&*(&&
}#%^%$%&*^(&

ActiveWorkbook.SaveAs Filename:="E:\HSA Template 2", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False, CreateBackup:=False

End Sub.

So where should I place the script?? before the end sub??


"Norman Jones" wrote:

Hi Ddiicc,

On the link page I gave you, Chip Pearson gives a DeleteAllVBA procedure
which 'does what it says on the label'.

Customising for your specific workbook, try:

Sub DeleteAllVBA()

Dim VBComp As VBIDE.VBComponent
Dim VBComps As VBIDE.VBComponents
Dim WB As Workbook

Set WB = Workbooks("HSA LGC01.xls")

Set VBComps = WB.VBProject.VBComponents

For Each VBComp In VBComps
Select Case VBComp.Type
Case vbext_ct_StdModule, vbext_ct_MSForm, _
vbext_ct_ClassModule
VBComps.Remove VBComp
Case Else
With VBComp.CodeModule
.DeleteLines 1, .CountOfLines
End With
End Select
Next VBComp

End Sub

'=====================================
Before using this code, thank Chip and carefully read the warning he
provided for the procedu
The procedure below will delete all the VBA code in a project. You should
use this procedure with care, as it will permanently delete the code.
Standard modules, user forms, and class modules will be removed, and code
within the ThisWorkbook module and the sheet modules will be deleted. You
may want to export the VBA code, using the procedure above, before deleting
the VBA code.
'<<=====================================

---
Regards,
Norman



"ddiicc" wrote in message
...
Hi Norman,
Thanks for responding, I am rather a beginner in macro writing,
So I would like to ask on how to add in the code that you given.
Eg..My ending of the macros is stated below.

ActiveWorkbook.SaveAs Filename:="E:\Jong\Python A HSA\HSA Yield For All
Lines 2005\C LGC\All HSA LGC Stations Yield\G Jul 2005 LGC Yield\HSA LGC01
All Model 1st Pass Yield Jul 2005.xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False, CreateBackup:=False

End Sub


So how do I add in the script you have given, I have about 11 modules to
delete
after the workbook is saved as stated on the above...

Regards ddiicc

"Norman Jones" wrote:

Hi Ddiicc,

Try:

Sub DeleteModule()
Dim VBComp As VBComponent
Set VBComp = Workbooks("Book2.xls").VBProject. _

VBComponents("NewModule")
Workbooks("Book2.xls").VBProject.VBComponents.Remo ve VBComp
End Sub


---
Regards,
Norman



"ddiicc" wrote in message
...
Hi Norman,

What If I want to delete a module in another workbook 2 using the
module
in
workbook 1... so how should go about writing this in workbook module???



"Norman Jones" wrote:

Hi Ddiicc,

See Chip Pearson VBE programming page at:

http://www.cpearson.com/excel/vbe.htm

See particularly the section entitled: Deleting A Procedure From A
Module


---
Regards,
Norman



"ddiicc" wrote in message
...
Hi to the experts in excel programming.

What is the commands or script for deleting a macro automatically
using
another workbook macro.

Hi Ddiicc,

Try:

'========================
Sub Tester02()

Dim WB As Workbook
Dim arr As Variant
Dim i As Long

arr = Array("SortData", "CrunchData")

Set WB = ActiveWorkbook

For i = LBound(arr) To UBound(arr)
DeleteModule2 WB, arr(i)
Next

WB.SaveAs Filename:="E:\HSA Template 2"

End Sub


Function DeleteModule2(theWB As Workbook, ModName)
Dim VBComp As VBComponent

Set VBComp = theWB.VBProject.VBComponents(ModName)

theWB.VBProject.VBComponents.Remove VBComp

End Function

'<<========================

As Chip Pearson explained in the link page. in workbook A you will ned to
set a reference in VBA to theMicrosoft Visual Basic for Applications
Extensibility library.

The above code should be placed in the AllDataYieldCrunch module of Workbook
A and replaces your exsting sub:

Sub ########()

####$##^^%&^
#$#%^$^&*(&&
}#%^%$%&*^(&

ActiveWorkbook.SaveAs Filename:="E:\HSA Template 2", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False, CreateBackup:=False

End Sub.

Before running this procedure, *ensure* that Workbook B is the active
workbbook!

---
Regards,
Norman



"ddiicc" wrote in message
...
Hi Norman,
I have try and try but still I receive some errors.

I will stated my examples clearly, so you could help me or give me the
words
to fill in the blanks.

"Workbook A" name is "HSA template 1"
"Workbook B" name is "HSA template 2"

Workbook A contains a module name "AllDataYieldCrunch"
Workbook B conains a 2 module name, 1st is "SortData", 2nd is "CrunchData"

So in my workbook A, I want to add the code, macro or scripts, inside my
module "AllDataYieldCrunch" together. Then it will also delete the 2
modules
in
workbook B.

Workbook A module stated below :-

Sub ########()

####$##^^%&^
#$#%^$^&*(&&
}#%^%$%&*^(&

ActiveWorkbook.SaveAs Filename:="E:\HSA Template 2", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False, CreateBackup:=False

End Sub.

So where should I place the script?? before the end sub??


"Norman Jones" wrote: