Email Excel without Macro
Email Excel without Macro
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Email Excel without Macro

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,169
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

  3. #3
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com