Edit Excel VBA with VBA

[PCloutier]

Can anyopne help me figure out how to edit a VBA project's code with code? Specifically, I want to delete all procedures in a sheet's code module by means of a VBA procedure in the same project.
Background: one sheet contains a pivot table. The sheet's code module contains a small procedure to refresh the pivot cache on sheet activate event and the procedure calls a number of preparatory procedures located in a standard module. But, I need to extract the sheet alone (i.e. without the sheet containing the database) to a new workbook to email a customer. Since only the sheet is saved in the new workbook and without the project's standard modules, the activate procedure cannot find the preparatory procedures so bugs.
I need to automate the extraction of the pivot table sheet to a new workbook also deleting the VBA code in the sheet's code module.
Thanks for any help.

[Tom Schreiner]

I remember seeing this topic before on this board.

A good reply was given with a link to a website, I beleive, which showed you how to purge your code out of a copy.

Do a search of this site...

Another option to check into.
Search VBA help for "Conditional Compilation"

Tom


[ This Message was edited by: TsTom on 2002-05-04 08:43 ]

[NateO]

Bury the following in an appropriate sheet module:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim ws As Worksheet
For Each ws In activeworkbook.Worksheets
    With activeworkbook.VBProject.VBComponents("sheet" & _
        ws.Index).CodeModule
                .DeleteLines 1, .CountOfLines
    End With
Next
End Sub

Hope this helps.

_________________
Cheers, NateO

[ This Message was edited by: NateO on 2002-05-04 09:31 ]

[NateO]

Actually, don't bury this in the sheet module!! You'll kill your existing procedures. Just pull the dim statement and the remainder of the code and put it in your procedure that moves the sheet[s] in question.

Incidentally P, any relation to Vancouver's goalie?

Sorry 'bout that and bon soir!

_________________
Cheers, NateO

[ This Message was edited by: NateO on 2002-05-04 09:52 ]

[Ivan F Moala]

Try this code ....change as required.
This will delete the ACTIVEWORKBOOKS Sheet
code....ie. After your routine Copies the
sheet to a new workbook then Run this code,
(AMENDED to suit)

Option Explicit



'*****************************************************************************

'* This procedure removes all code the activeworkbook Sheet code you specify.

'*

'* Reference to the VB Extensibility library is Automatically set when

'* 1st run via the GUID routine.

'*

'* Removes from Workbooks all:

'* Code in sheet and workbook modules = vbext_ct_Document

'* Ivan F Moala 02/09/2001

'*****************************************************************************



Sub RunThisFirst()

On Error Resume Next  '// if it already exits

ThisWorkbook.VBProject.References.AddFromGuid _

    "{0002E157-0000-0000-C000-000000000046}", 5, 0

On Error GoTo 0



TellUser



Remove_SheetCode



End Sub



Sub Remove_SheetCode()

'// XL2K:

'// Dim VBComp As VBComponent, AllComp As VBComponents, ThisProj As VBProject

'// XL97 & XL2K:

Dim VBComp As Object, AllComp As Object, ThisProj As Object

Dim RemoveOK As Integer, Wbk As Workbook



If ActiveWorkbook.Name <> ThisWorkbook.Name Then

    '// Ask the User Just in case!

    RemoveOK = MsgBox("Remove All Sheet code from:= " & ActiveWorkbook.Name & " ?", vbYesNo)

    If RemoveOK = vbNo Then Exit Sub

    Set ThisProj = ActiveWorkbook.VBProject

    Set AllComp = ThisProj.VBComponents



    For Each VBComp In AllComp

        With VBComp

            Select Case .Type

                Case 100

                '// Remove Event codes

                    .CodeModule.DeleteLines 1, .CodeModule.CountOfLines

            End Select

        End With

    Next

End If



Set ThisProj = Nothing

Set AllComp = Nothing



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 the ActiveWorkbook!" & 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

If All else Fails;


_________________
Kind Regards,
Ivan F Moala
Have a Nice day

[ This Message was edited by: Ivan F Moala on 2002-05-04 15:17 ]

[Jack in the UK]

ivan my old mate,

dam fine post, is that really you?

[Ivan F Moala]

On 2002-05-05 01:42, Jack in the UK wrote:
ivan my old mate,

dam fine post, is that really you?

No ?! .....although there can be times I feel
like doing that...

[PCloutier]

Thanks for your replies:

TsTom: thanks for the conditional compilation suggestion. Might help if I can figure out how to use it.

NateO: thanks for the code suggestion. I'll look into it. Nope, not Vancouver goalie, but was born and raised in nearby Seattle although now live in Geneva, Switzerland.

Ivan: thanks for the code suggestion. I've got to study it before I try it out.

Thanks again,

[NateO]

P, Seattle's a nice spot, used to live outside of Vancouver myself. A slightly different format:

Sub getrid()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
    With ActiveWorkbook.VBProject.VBComponents(ws.CodeName).CodeModule
                .DeleteLines 1, .CountOfLines
    End With
Next ws
End Sub

_________________
Cheers, NateO

[ This Message was edited by: NateO on 2002-05-07 10:02 ]

[Chris Davison]

On 2002-05-05 01:42, Jack in the UK wrote:
ivan my old mate,
dam fine post, is that really you?

Jack....

this image is a small part of a large mpeg. I can't remember the full story, but the guy had been under surveillance for about a year as he had caused thousands of dollars of repairs to his equipment (it may have been IBM or NASA or somewhere blue-chip like that) so they installed cameras to look at his cubicle...

The full mpeg shows him banging his keyboard, then hitting the screen and cursing.... finally, he gets up, picks up his keyboard and hits the moniter with it, sending it flying, with a totally bemused co-worker looking on in amazement into his cubicle.

I believe he was fired.

I'll try to pull it out for you, I know I have the full copy on my other computer here (disconnected)