Save without VBA

[prasadk]

Hi,

Is it possible save the excel sheet without the VBA, when saved in different names,

How should I go about it. Thankyou.

 

[stevebausch]

Ctrl-N (creates new workbook)


(Go back to donor workbook, select the sheet desired)
Ctrl-A
Ctrl-C

Select the new workbook, and on the page you desire to paste:

Ctrl-A
Ctrl-V

When you created the workbook of worksheets only, no modules, then Save As.
This message was edited by stevebausch on 2002-10-09 10:22

 

[stevebausch]

oops
This message was edited by stevebausch on 2002-10-09 08:14

 

[prasadk]

Hi Steve,

Was it Oops, I'm a bit confused, not a 'small bit - but a Big Oops'

What I'm trying to do is to save it in a different file name reference to date and time, how should I avoid the save of VBA part in the existing file.

Thankyou

 

[stevebausch]

You lost me.

If that doesn't work, maybe the workbook already exists, and it's a simple matter of removing the modules?

I "work smart" on project aspects that are permanent, or impact others. When it comes to prepping a workbook to not have modules, I have no shame removing them manually.

 

[prasadk]

Hi Steve,

Sorry if I have offended you.

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.

Thankyou. Really appreciate any help.

 

[dtaylor]

I had a similar situation and was guided to this site

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

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!

good luck

dan
This message was edited by dtaylor on 2002-10-10 15:14

 

[dtaylor]

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.


Sub DeleteAllVBA()
'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, _
vbext_ct_ClassModule
VBComps.Remove VBComp
Case Else
With VBComp.CodeModule
.DeleteLines 1, .CountOfLines
End With
End Select
Next VBComp

End Sub

 

[prasadk]

Thanks Dan. I'll try your code. The website you recommend had lot of stuff to learn.

Thanks.

 

[stevebausch]

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