#1
|
|||
|
|||
Is it possible to use a vba macro to delete other macros?
I have successfully created individual macros that update 20 individual spreadsheets. These macros are all stored in a template used daily. For storage reasons, I want to delete the individual macros when the day comes to an end. No need to store 20 macros with each day's workbook when they are in the template. Each macro has a button to activate it the I delete (cut) once it has been run. I would like to be able to easily delete all the actual macros (modules) when the day's work is completed. I believe the macros can be 'hidden' since there is a button to activate it but that doesn't reduce the storage space needed. It's not much but after 365 days a year it does add up. |
#2
|
|||
|
|||
https://www.youtube.com/watch?v=0u9BsdpEO1I
What is the command in Excel VBA to delete a specific macro? - Stack Overflow Here are two macros : Code:
Option Explicit 'Reference : "Microsoft Visual Basic For Applications Extensibility" Sub RemoveSheetModule() Dim wb As Workbook Dim md As Module Dim str As String Application.ScreenUpdating = False Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & "URL Check - Copy.xlsm") For Each md In wb Select Case md.Name Case "Module1" str = md.CodeName With wb.VBProject.VBComponents(str).CodeModule .DeleteLines 1, .CountOfLines End With Case Else End Select Next md wb.Close True Application.ScreenUpdating = True MsgBox "Done...", 64 End Sub Sub delModMacro() Dim wb As Workbook Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & "CommandButton.xlsm") With wb.VBProject.VBComponents("Module1").CodeModule '<--- change to ThisWorkbook for that module .DeleteLines 1, .CountOfLines End With MsgBox "Done...", 64 End Sub |
#3
|
|||
|
|||
Code:
Option Explicit 'Reference : "Microsoft Visual Basic For Applications Extensibility" Sub RemoveSheetModule() Dim wb As Workbook Dim md As Module Dim str As String Application.ScreenUpdating = False Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & "URL Check - Copy.xlsm") For Each md In wb Select Case md.Name Case "Module1" str = md.CodeName With wb.VBProject.VBComponents(str).CodeModule .DeleteLines 1, .CountOfLines End With Case Else End Select Next md wb.Close True Application.ScreenUpdating = True MsgBox "Done...", 64 End Sub Sub delModMacro() Dim wb As Workbook Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & "CommandButton.xlsm") With wb.VBProject.VBComponents("Module1").CodeModule '<--- change to ThisWorkbook for that module .DeleteLines 1, .CountOfLines End With MsgBox "Done...", 64 End Sub |
#4
|
||||
|
||||
Whilst you can remove code via VBA wouldn't it be more straightforward to convert the file to xlsx and remove the xlsm version?
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#5
|
|||
|
|||
As I researched more into the issue, I am leaning this direction. With this, I'm wondering if it can be set up so that the .xslm document is delete automatically so that the correct document is the one uploaded into the permanent record.
|
#6
|
||||
|
||||
There will be a Catch 22 with implementing that idea: Not possible to delete the file while it is open AND the file needs to be open while the macro is running.
The usual method to do this would be to create the macro and store it in a different workbook (or your Personal.xlsb) so the code never resides in the template workbook.
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#7
|
|||
|
|||
That takes me back to a problem I have tried to get around before. How to call to the workbook from the personal.xlsb when the workbook's name is different each day.
|
#9
|
|||
|
|||
I have now replied to that particular post. Thanks for the reminder.
|
Tags |
delete module, macro help |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
A few macros or functions run in a single macro? | pentagram_31 | Word VBA | 15 | 10-29-2018 01:46 PM |
Compatibility of 2 macros in mail merge: Delete table rows + save individual PDFs | Btop | Word VBA | 26 | 03-07-2018 01:45 PM |
URGENT: Macro to open document and apply other macros successively | Krategy | Word VBA | 1 | 12-02-2016 04:41 PM |
VBA Powerpoint Macros: Set String in one Macro, use it in another | Martijn6134 | PowerPoint | 0 | 11-09-2016 05:36 AM |
Macro to find a word in first row of table and then perform two macros | hmsrose | Word VBA | 5 | 01-30-2015 12:17 AM |