Excel Macro to delete the Macro?

I have a feeling this can't be done.....

Excel File with Macro (that does loads of stuff.....etc....)
Then saves as a new file name.

When a new user opens the resulting file - they still get the 'enable amcro' option.

I don't want users to alter their security.

Is there a way to force the Macro to:
1- Do its Stuff
2-Save as new filename
3-Delete itself
4-Then Resave

Its part 3 that is niggling me.....

I don't want less IT-persons to see that message as many freak out thinking that its a spawn of the Y2Killer-Syndrome Wormathon Bug trying to destroy their PC..... when will they learn.....
flickimpAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chris BottomleySoftware Quality Lead EngineerCommented:
I would imagine control macro A which calls macro B.  Where macro B is what you want to delete and the last item in macro A for instance deletes macro B or module containing macro b.

See http://www.cpearson.com/excel/vbe.aspx for deletion of vba modules/code etc.

Chris
0
James ElliottManaging DirectorCommented:
Something like this might work. This needs to go in a module named "Module1"

Option Explicit

Sub SaveAsWithoutVB()
    Dim lLineCount As Long
   
ThisWorkbook.SaveAs (ThisWorkbook.Path & "\test2")

With ActiveWorkbook
    lLineCount = .VBProject.vbcomponents.Item("Module1").codemodule.countoflines
    .VBProject.vbcomponents.Item("Module1").codemodule.DeleteLines 1, lLineCount
    .Save
End With

End Sub
0
flickimpAuthor Commented:
Hi
The macro is already in module 1, so does your extra bit need to come after that?

Or before my current macro's End Sub?

0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

flickimpAuthor Commented:
This is what my current macro looks like.... where do I add the extra parts?
Sub myRefresh()
 
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
 
    ActiveWorkbook.RefreshAll
 
    Sheets(Array("MONTH Data", "MONTH <65", "MONTH 65+")).Select
    
    Sheets("MONTH Data").Activate
    Cells.Select
    Cells.EntireColumn.AutoFit
    
    Range("A1").Select
    
    Sheets("Macro").Select
    Range("A5").Select
 
    Sheets("REPORT").Select
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    Sheets("MONTH Data").Select
    Application.CutCopyMode = False
    ActiveWindow.SelectedSheets.Delete
     
    Sheets("REPORT <65 Years").Select
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    Sheets("MONTH <65").Select
    Application.CutCopyMode = False
    ActiveWindow.SelectedSheets.Delete
    
    Sheets("REPORT 65+ Years").Select
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    Sheets("MONTH 65+").Select
    Application.CutCopyMode = False
    ActiveWindow.SelectedSheets.Delete
    
    Sheets("MACRO").Select
    Range("A1").Select
    
    ThisFile = Range("B5").Value
    ActiveWorkbook.SaveAs Filename:=ThisFile
    Sheets("Macro").Select
    ActiveWindow.SelectedSheets.Delete
    
    ActiveWorkbook.Save
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
                
End Sub

Open in new window

0
flickimpAuthor Commented:
See below.... have added the code.

it does delete Module 1, but when I open the resultiung file....I still get that enable/disable macro warning.????
    Sheets("MACRO").Select
    Range("A1").Select
    
    ThisFile = Range("B5").Value
    ActiveWorkbook.SaveAs Filename:=ThisFile
    Sheets("Macro").Select
    ActiveWindow.SelectedSheets.Delete
    
    ActiveWorkbook.Save
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    
Dim lLineCount As Long
    
With ActiveWorkbook
    lLineCount = .VBProject.vbcomponents.Item("Module1").codemodule.countoflines
    .VBProject.vbcomponents.Item("Module1").codemodule.DeleteLines 1, lLineCount
    .Save
End With
 
    ActiveWorkbook.Save
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
 
End Sub

Open in new window

0
James ElliottManaging DirectorCommented:
hmm, quite odd.

How many users are we talking about? Would you be prepared to turn the macro warning 'off' on each computer?

Jell

0
Rory ArchibaldCommented:
If you don't have any code behind the worksheets themselves, you could simply copy the sheets to a new workbook and save that - then the module won't go with it.
Regards,
Rory
0
Chris BottomleySoftware Quality Lead EngineerCommented:
Are you using an old version of excel?, I recall that with Visio macros if the module exists that it still sets up the macro warning.  You could test it by deleting module1 from the VBE.  If this is it then I redirect you to delete the module i.e as mentioned earlier  ..

    Sub DeleteModule()
        Dim VBProj As VBIDE.VBProject
        Dim VBComp As VBIDE.VBComponent
   
        Set VBProj = ActiveWorkbook.VBProject
        Set VBComp = VBProj.VBComponents("Module1")
        VBProj.VBComponents.Remove VBComp
    End Sub

Chris
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott HelmersVisio Consultant, Trainer, Author, and DeveloperCommented:
Chris is right about needing to delete the module not just the lines of code contained therein in order to prevent the macro warning from appearing.

One other alternative -- and I realize that what I'm suggesting requires a non-trivial upgrade decision: you could upgrade to Excel 2007. With 2007, the default file type (*.xlsx instead of *.xls) doesn't allow macros, i.e., you can't even save a "normal" spreadsheet when there is macro code in it. You are required to save as a new macro-enabled file type, *.xlsm if you want macros to work. Consequently, if you save your spreadsheet as *.xslx, there will be no active macro code.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.