Leave 2 macros after copy

[David]

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

[Tom Ogilvy]

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" <[email protected]> wrote in message
news:%[email protected]...
> 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

[David]

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

[David]

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