Why I want to remove the VBA part automatically is because this file is being saved 3 times a day in different times. It is used to collect data through DDE. The saving is done through VBA itself. And the end user who opens the file for consolidation of data don't require the modules inside. It is just eating up the hard disk space.
you will lots of code to do just what you are asking..and it works I incorp into many of my apps since I have the same problem..people that do not want to see the enable macro or just don't know what to do!
dan This message was edited by dtaylor on 2002-10-10 15:14
Yes this is possible. Here is a sample of code I use.
you will need to set a reference to:
Microsoft Visual Basic for Applications Extensibitlty 5.3
This is powerfull and will delete all code from all open workbooks. Close any workbooks not needed.
'Deletes all moduels, forms and sheet modules
Dim VBComp As VBIDE.VBComponent
Dim VBComps As VBIDE.VBComponents
Set VBComps = ActiveWorkbook.VBProject.VBComponents
For Each VBComp In VBComps
Select Case VBComp.Type
Case vbext_ct_StdModule, vbext_ct_MSForm, _
.DeleteLines 1, .CountOfLines
You could also park all the VBA code in a Personal workbook.
Do you start the macros with buttons, or from the Macro Dialog?
You see the concept? You should be able to call a macro, whereever it is located, as long as the buttons, etc. have the proper assignment.
So, you should be able to pare the workbook code down to a bare minimum. Just make sure the macro modules DO NOT have the Option Private Module statement within them.
And Prasad Prabhu, you need to develop the habit of defining the project in it's entirety, from the first moment you solicit assistance.
I realize you may want to foster a "buy-in" to assisting you, and you don't want to scare anyone off; therefore the "in like a needle, out like a plow" scheme. This message was edited by stevebausch on 2002-10-10 20:58