Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Leave 2 macros after copy
Each month I backup a workbook with only values intact. As part of that
backup routine, I use the following to strip the backup of ALL code: Set VBComps = ActiveWorkbook.VBProject.VBComponents For Each VBComp In VBComps With VBComp.CodeModule ..DeleteLines 1, .CountOfLines End With Next VBComp Short, concise and non-convoluted. I've since added a couple of macros that I wish to remain in the backup to avoid having to later copy/paste them from the original. Without greatly enlarging the code-stripping routine, can I retain those 2 macros/procedures? I would even entertain putting them in a separate module if I knew how to easily strip all the other code out. I have 'ThisWorkbook' code, UserForms and 'Module' code in the original file. I anticipate the suggestion just to run those two procedures before performing the backup, but by necessity I need to be able to run them several days after the backup is performed. Any help? -- David |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Leave 2 macros after copy
Put them in a separate module and skip that module in your code.
Sub AAA() Set VBComps = ActiveWorkbook.VBProject.VBComponents For Each VBComp In VBComps If VBComp.Name < "Module3" Then With VBComp.CodeModule .DeleteLines 1, .CountOfLines End With End If Next VBComp End Sub -- Regards, Tom Ogilvy "David" wrote in message ... Each month I backup a workbook with only values intact. As part of that backup routine, I use the following to strip the backup of ALL code: Set VBComps = ActiveWorkbook.VBProject.VBComponents For Each VBComp In VBComps With VBComp.CodeModule .DeleteLines 1, .CountOfLines End With Next VBComp Short, concise and non-convoluted. I've since added a couple of macros that I wish to remain in the backup to avoid having to later copy/paste them from the original. Without greatly enlarging the code-stripping routine, can I retain those 2 macros/procedures? I would even entertain putting them in a separate module if I knew how to easily strip all the other code out. I have 'ThisWorkbook' code, UserForms and 'Module' code in the original file. I anticipate the suggestion just to run those two procedures before performing the backup, but by necessity I need to be able to run them several days after the backup is performed. Any help? -- David |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Leave 2 macros after copy
Tom Ogilvy wrote
Put them in a separate module and skip that module in your code. Sub AAA() Set VBComps = ActiveWorkbook.VBProject.VBComponents For Each VBComp In VBComps If VBComp.Name < "Module3" Then With VBComp.CodeModule .DeleteLines 1, .CountOfLines End With End If Next VBComp End Sub Simple solution. Many thanks. I may use it in the future. Actually, a lightbulb lit when I realized that since I used the line: ActiveWorkbook.Sheets.Copy instead of ActiveWorkbook.Copy none of the modules or forms were copied to the new book anyway, so I didn't need to delete anything since I had no sheet level code. So I looked at Chip Pearson's .vbe page and used his method of copying a module after putting the desired routines in the separate Module2: With ThisWorkbook FName = .Path & "\code.txt" ..VBProject.VBComponents("Module2").Export FName End With ActiveWorkbook.VBProject.VBComponents.Import FName Kill FName -- David |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Leave 2 macros after copy
David wrote
Simple solution. Many thanks. I may use it in the future. Actually, a lightbulb lit when I realized that since I used the line: ActiveWorkbook.Sheets.Copy instead of ActiveWorkbook.Copy none of the modules or forms were copied to the new book anyway, so I didn't need to delete anything since I had no sheet level code. So I looked at Chip Pearson's .vbe page and used his method of copying a module after putting the desired routines in the separate Module2: With ThisWorkbook FName = .Path & "\code.txt" .VBProject.VBComponents("Module2").Export FName End With ActiveWorkbook.VBProject.VBComponents.Import FName Kill FName I shouldn't have said "instead of ActiveWorkbook.Copy", since you can't do that anyway. What I should have said is: Since there is no sheetlevel code and ActiveWorkbook.Sheets.Copy doesn't copy any modules/forms, there is nothing to delete anyway, so I could do away with all that VBComp stuff and replace it with the Module copy code. -- David |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy Sheet #'s, but leave cell reference | Excel Discussion (Misc queries) | |||
Macros: can you copy macros from one doc to another? | Excel Discussion (Misc queries) | |||
using IF function to copy, but leave populated cell alone | Excel Worksheet Functions | |||
How to copy paste from Excel and leave the pasted data editable? | Excel Worksheet Functions | |||
weird saving of a document with macros resulting with macros being transfered to the copy | Excel Programming |