Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to delte macros in another workbook
Hi,
Here I got following VBA code to delete macros from another opened workbook. Public Sub DeleteAllVBA(wbk As Workbook) On Error Resume Next Dim wbkTemp As Workbook Dim VBComps Dim VBComp Set wbkTemp = Workbooks(wbk.Name) Set VBComps = wbkTemp.VBProject.VBComponents For Each VBComp In VBComps Debug.Print VBComp.Name Select Case VBComp.Type Case vbext_ct_StdModule, vbext_ct_MSForm, vbext_ct_ClassModule VBComps.Remove VBComp Case Else With VBComp.CodeModule .DeleteLines 1, .CountOfLines End With End Select Next VBComp End Sub Actually my requirement is, I'm having some macros (sheet, this workbook evevents and standard modules) in Book1.xls including above code. When I click a button on sheet1 it creates another .xls file Button event starts here ... It creates a copy of 'Book1.xls' with 'Book1_*.xls' - (*-last modified date & time) using File system object. Once it is created I'm opening the newly created file ('Book1_*.xls') and calling 'DeleteAllVBA' function to delete all macros. But it is deleting only the code in sheets, thisworkbook and some of the standard moudles i.e., not all modules. later closing newly created file ('Book1_*.xls'). Button event ends upto here ... Then opened 'Book1_*.xls', I found that some of the modules are not deleted, while this work book is opend in this situation I executed 'DeleteAllVBA' by passing file name at this time its removed all the modules. but its not done in button event. I executed the same code (button event code) in debug mode, its executing 'VBComps.Remove VBComp' but it is not deleting the modules Can any one help me out, what is root cause in not deleting the modules. Regards Vinod |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
delte multiple blank rows in excel | Excel Worksheet Functions | |||
deleting macros that are not really in my workbook? | Excel Worksheet Functions | |||
How do I save a workbook as a new workbook by using macros? | Excel Discussion (Misc queries) | |||
Removing macros from workbook | Excel Discussion (Misc queries) | |||
Sharing workbook with macros | Excel Discussion (Misc queries) |