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

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

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

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