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.

 

[Legacy 98055]

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,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>

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 in question.

Incidentally P, any relation to Vancouver's goalie?

Sorry 'bout that and bon soir!

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>

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)<pre/>
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</pre>


If All else Fails;

_________________
Kind Regards,<font size=+2><font color="red"> I<font color="blue">van<font color="red"> F M</font color="blue">oala</font><MARQUEE>Have a Nice day

</MARQUEE>
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,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>

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)