Delete Macro in VBA

[garyrpollitt]

How do I delete a macro in VBA.(or a module)
I run a macro that imports a txt file into Excel then saves it as another name, but I do not want it to save the macro.I need the macro deleted before saving.
Gary

[ This Message was edited by: garyrpollitt on 2002-05-15 02:53 ]

[Mudface]

Rather than delete your code, use Workbooks.Add to create a new workbook and import your data into that.

[garyrpollitt]

Cheers Mudface
But that means me creating a new macro in the new workbook to import the data. I only want one macro on a spreadsheet and that is in "Test1".
Basically "Test1" imports txt data, creates a graph then saves it as a eg "122340".Test1 closes keeping the original macro with nothing on its sheets. But 122340 now also has the macro.
Hope you understand!!
Gary
As soon as Test1 is saved as 122340 then 122340 gets the focus and I want to delete the Macro on 122340

[Ivan F Moala]

On 2002-05-15 04:34, garyrpollitt wrote:
Cheers Mudface
But that means me creating a new macro in the new workbook to import the data. I only want one macro on a spreadsheet and that is in "Test1".
Basically "Test1" imports txt data, creates a graph then saves it as a eg "122340".Test1 closes keeping the original macro with nothing on its sheets. But 122340 now also has the macro.
Hope you understand!!
Gary
As soon as Test1 is saved as 122340 then 122340 gets the focus and I want to delete the Macro on 122340

Hi Gary
You could try this code.
BUT be warned...Backup your Files...this
macro will delete ALL Code and Rferences.

Suggest you Save Copies and test extensively.

Place code in Std Module and call it
when you need. You may need to put in Flags
of some sought to ensure that it doesn't get
called EXCEPT when you want it too.

Sub RemoveAllCode()

On Error Resume Next

'Make reference to Extensibilty Library

ThisWorkbook.VBProject.References.AddFromGuid _

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

On Error GoTo 0



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

Dim ThisRef As Reference, WS As Worksheet, Dlg As DialogSheet



Set ThisProj = ActiveWorkbook.VBProject

Set AllComp = ThisProj.VBComponents

    For Each VBComp In AllComp

        With VBComp

            Select Case .Type

                Case 1, 2, 3

                    AllComp.Remove VBComp

                Case vbext_ct_Document

                    .CodeModule.DeleteLines 1, .CodeModule.CountOfLines

            End Select

        End With

    Next

    

    For Each ThisRef In ThisProj.References

        If Not ThisRef.BuiltIn Then ThisProj.References.Remove ThisRef

    Next



    Application.DisplayAlerts = False

    For Each WS In Excel4MacroSheets

        WS.Delete

    Next

    For Each Dlg In DialogSheets

        Dlg.Delete

    Next



End Sub

[Mudface]

Well, you're basically doing the same thing, but instead of using the macro to fill the workbook containing the macro with data, you would create a new workbook, fill that with your data and then save it.

You do this by using references to your new workbook in the macro, rather than references to the current one. As an example, try out the following two procedures, the first fills sheet 1 of the workbook containing the macro with data: -

Public Sub FillCurrentWorkbook()
Dim x As Integer

For x = 1 To 100
ThisWorkbook.Sheets(1).Range("A" & x).Value = x
Next x

End Sub

The second adds a new workbook and fills it with data: -

Public Sub FillNewWorkbook()
Dim x As Integer
Dim mycount As Integer

Workbooks.Add
' Find out the index of the new workbook so we can refer to it
' You could also do this with ActiveWorkbook, but this seems better

mycount = Workbooks.Count

For x = 1 To 100
Workbooks(mycount).Sheets(1).Range("A" & x).Value = x
Next x

End Sub

Do you see what I mean? You just need to rewrite your code to do everything to the newly cretaed workbook.

[Jack in the UK]

Hi Ivan –

Do you know if this will work on stealth, (hope you twig me here)

As useful!