how do i send a workbook without the vba (SOLVED)

[kskinne]

I need to figure out a way to send a workbook as an attachment in excel, but the attachment cannot include any of the vba code that is in the original workbook. can this be done?

thanks,
kevin
This message was edited by kskinne on 2002-10-29 10:00

 

[Mauricio Samy Silva]

Hi Kevin,
I know two ways to place my macros out of view by others.
1st. - I recorder my macros into a personal file for macros and not send that file of course!
2nd. - I proctect my macros by a "password".
Hope this help you!

 

[stevebausch]

Are you trying to avoid a limit on attachment size?

You can copy and paste the sheet into a blank workbook.

 

[kskinne]

actually, i was wondering if there was a way to delete all vba codes with a vba code that I could perhaps store in my personal.xls and run from there. i know that ASAP utilities has an option for removing all macros/vba code from a workbook, but i need to actually see the code behind it, because i want to make it button-driven, and combine it with some other code as well.

thanks,
kevin

 

[XL-Dennis]

kskinne,

Have a look in Chip PEarson's side. He has a procedure that delete all modules in the active workbook which You may revise to fit Your purpose:

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

Kind regards,
Dennis

 

[kskinne]

i'll take a look at the code on that site - thank you

kevin

 

[dtaylor]

the site the XL-Dennis provide is excellent. I just completed a project that required just thi..went to this site and incorp the code into my app., worked like a charm!
dan

 

[kskinne]

I copied the following code off of the site to delate all vba from the workbook:

Sub DeleteAllVBA()
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

However, when I run this, I am receiving an error msg: "Compile error: user-defined type not-defined". What specifically is in this bit of code that I need to define further for it to run properly? Any help with this code would be appreciated, as is all the help given thus far. thanks,

kevin

 

[Mudface]

From right at the top of the web-page: -

"Before using these procedures, you'll need to set a reference in VBA to the VBA Extensibility library. In the VBA editor, go to the Tools menu, choose the References item, and put a check next to "Microsoft Visual Basic For Applications Extensibility" library. "

 

[XL-Dennis]

kskinne,

You need to set a reference to the
Microsoft Visual Basic for Applications Extensibility 5.3 via Tools | reference in teh VB-editor.

Kind regards,
Dennis