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.

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

</pre>

 

[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!