Email Excel without Macro

[johnou]

I would like to send out the current Excel file without the VBA coding because it contains system password etc. Is that any way I can do that?

Thanks,

John

[Jack in the UK]

HI--

I do not know of such a way, password portect the modules if macros there, but thats no very safe for advanced users, bit like car raido and wondow left open!

or copy the sheet to new work book and mail that so VBA will not be attached

This assumes the VBA is not needed of cause

[Ivan F Moala]

On 2002-03-03 06:24, johnou wrote:
I would like to send out the current Excel file without the VBA coding because it contains system password etc. Is that any way I can do that?

Thanks,

John

Hi John, if you want to remove all code from a workbook then you try this.

Run this routine with the workbook that
you want to delete code from open... don't
worry if you have other workbooks open
it will prompt you to OK the deletion.

You don't have to referene the Libray files
This is automatically taken care of.

Option Explicit

'*****************************************************************************
'* This procedure removes all code and related structures from a workbook.
'*
'* Reference to the VB Extensibility library is Automatically set when
'* 1st run via the GUID routine.
'*
'* Removes from Workbooks all:
'* Regular modules = vbext_ct_StdModule
'* Class modules = vbext_ct_ClassModule
'* Userforms = vbext_ct_MSForm
'* Code in sheet and workbook modules = vbext_ct_Document
'* Non built-in references
'* Excel 4 macro sheets
'* Dialog sheets
'*
''

Sub RunThisFirst()
On Error Resume Next 'if it already exits
ThisWorkbook.VBProject.References.AddFromGuid _
"{0002E157-0000-0000-C000-000000000046}", 5, 0
TellUser
RemoveAllCode
End Sub

Sub RemoveAllCode()
'XL2K:
'Dim VBComp As VBComponent, AllComp As VBComponents, ThisProj As VBProject
'XL97 & XL2K:
Dim VBComp As Object, AllComp As Object, ThisProj As Object
Dim ThisRef As Reference, WSht As Worksheet, Dlg As DialogSheet
Dim RemoveOK As Integer, Wbk As Workbook

For Each Wbk In Workbooks
If Wbk.Name <> ThisWorkbook.Name Then
'Ask the User Just in case!
RemoveOK = MsgBox("Remove All code from:= " & Wbk.Name & "?", vbYesNo)
If RemoveOK = vbNo Then GoTo Nxt
Set ThisProj = Wbk.VBProject
Set AllComp = ThisProj.VBComponents

For Each VBComp In AllComp
With VBComp
Select Case .Type
Case 1, 2, 3
'remove modules,Classes & Forms
AllComp.Remove VBComp
Case 100
'Remove Event codes
.CodeModule.DeleteLines 1, .CodeModule.CountOfLines
End Select
End With
Next
'remove References to other projects
For Each ThisRef In ThisProj.References
If Not ThisRef.BuiltIn Then ThisProj.References.Remove ThisRef
Next
End If

Set ThisProj = Nothing
Set AllComp = Nothing

Application.DisplayAlerts = False

'Remove Excel4 Macrosheets
For Each WSht In Wbk.Excel4MacroSheets
WSht.Delete
Next

'remove Dialog sheets-note dialogstypically hidden
For Each Dlg In Wbk.DialogSheets
Dlg.Delete
Next

Application.DisplayAlerts = True

Nxt: Next

MsgBox "Done!" & Space(20), vbInformation + vbSystemModal

End Sub

Sub TellUser()
Dim msg As String
Dim Proceed As Integer

msg = "This routine will delete ALL Code" & vbCr
msg = msg & "from ALL currently open workbooks!" & vbCr
msg = msg & "If you do not want to delete them then" & vbCr
msg = msg & "answer NO at the prompt to delete the code." & vbCr & vbCr
msg = msg & "To start this routine click YES" & vbCr
msg = msg & "To cancel this routine Click NO" & vbCr

Proceed = MsgBox(msg, vbInformation + vbYesNo, "Proceed")
If Proceed = vbNo Then End
End Sub

HTH

Ivan