D
davidm
The following code is designed as an integral part of a Project to carry
out periodical cleanings "in-house"in a project. It is linked with
built-in time scheduling to achieve this. If all is fine the goal is to
husband and manage file bloat as the Project Add-in files are liable to
frequent blow-outs from constant use.While it has lesser efficiency
than the popular standalone cleaners (a la Rob Bovey ), this artifice
brings along convenience of some value. But it has a few raw edges
which I need forumites to straighten out. The Code is adapted from Chip
Pearson's.
Sub CleanCode()
Dim c as vbComponent
Dim VComps as VBComponents
Dim Fname as String
For each c in ThisWorkBook.VbProject.VbComponents
If c.Type <>vbext_ct_document Then 'exclude Sheets/ThisWkBk modules
Fname= ThisWorkBook.Path & "\" & c.Name & ".txt"
c.Export Fname
vComps.Import Fname 'import them back
Kill Fname
Next
End Sub
When the above code is invoked, 2 worrying things happen. First, While
the contents of all the standard modules, Class Modules and UserForms
are duly exported as text files to the folder (shared by the
ActiveBook), upon importing these back, all the exported files
including the Userform txt.files are transferred back as expected BUT
the Userform frx files stay back in the folder only to build up
jetsams and clutter over time (barring manual weeding). For some
reason, the command, Kill Fname, executes its charges selectively!
Second, since my CodeCleaning procedure resides in one of the modules
in the Project, exporting itself and importing itself back generates a
kink in the entire process. Would that the CodeCleaner procedure
operates fom outside the Project and transfers modules from A to B and
back, this crisis would have been averted. But then we would have lost
the beauty of having a "home-based" cleaner.
Iwelcome any help.
David
out periodical cleanings "in-house"in a project. It is linked with
built-in time scheduling to achieve this. If all is fine the goal is to
husband and manage file bloat as the Project Add-in files are liable to
frequent blow-outs from constant use.While it has lesser efficiency
than the popular standalone cleaners (a la Rob Bovey ), this artifice
brings along convenience of some value. But it has a few raw edges
which I need forumites to straighten out. The Code is adapted from Chip
Pearson's.
Sub CleanCode()
Dim c as vbComponent
Dim VComps as VBComponents
Dim Fname as String
For each c in ThisWorkBook.VbProject.VbComponents
If c.Type <>vbext_ct_document Then 'exclude Sheets/ThisWkBk modules
Fname= ThisWorkBook.Path & "\" & c.Name & ".txt"
c.Export Fname
vComps.Import Fname 'import them back
Kill Fname
Next
End Sub
When the above code is invoked, 2 worrying things happen. First, While
the contents of all the standard modules, Class Modules and UserForms
are duly exported as text files to the folder (shared by the
ActiveBook), upon importing these back, all the exported files
including the Userform txt.files are transferred back as expected BUT
the Userform frx files stay back in the folder only to build up
jetsams and clutter over time (barring manual weeding). For some
reason, the command, Kill Fname, executes its charges selectively!
Second, since my CodeCleaning procedure resides in one of the modules
in the Project, exporting itself and importing itself back generates a
kink in the entire process. Would that the CodeCleaner procedure
operates fom outside the Project and transfers modules from A to B and
back, this crisis would have been averted. But then we would have lost
the beauty of having a "home-based" cleaner.
Iwelcome any help.
David