Sub ExportImportModulesToClean()
Dim x As VBComponent
Dim VBComps As VBComponents
Set VBComps = WORKBOOKS("TEST.XLA").VBProject.VBComponents
For Each x In VBComps
If x.Type = vbext_ct_MSForm Then
FName = ActiveWorkbook.Path & "\" & x.Name & ".frm"
FName1 = Replace(FName,".frm",".frx")
End If
If x.Type = vbext_ct_stdmodule Then
FName = ActiveWorkbook.Path & "\" & x.Name & ".std"
End if
If x.Type = vbext_ct_classmodule Then
FName = ActiveWorkbook.Path & "\" & x.Name & ".cls"
End if
x.Export FName
'delete old modules
VBComps.Remove x
VBComps.Import FName
Kill FName
If x.Type = vbext_ct_MSForm Then Kill FName1
Next
End Sub
--
Regards,
Tom Ogilvy
"davidm" wrote in
message ...
The code below which mimics standard CODE CLEANERS (1) exports all
(non-sheet based) modules in WORKBOOK("TEST.XLA") as text files etc.,
(2) deletes the modules and (3) then imports back the files as new
modules (The text files in folder are also meant to be deleted). The
whole idea is to clean up the codes in the WORKBOOK so to reduce the
file size which gets bloated over time. The problem I face is that the
code works fine except that when there are Userforms, the copies created
in the directory folder fail to be removed by the *Kill filename *
command. Notice that, for any one Userform, two (2) FRX files are
created even where one file extension (.frm) is used. I would
appreciate any help.
Here is the code (adapted from C.Pearson's):
Sub ExportImportModulesToClean()
Dim x As VBComponent
Dim VBComps As VBComponents
Set VBComps = WORKBOOKS("TEST.XLA").VBProject.VBComponents
For Each x In VBComps
If x.Type = vbext_ct_MSForm Then
FName = ActiveWorkbook.Path & "\" & x.Name & ".frm"
End If
If x.Type = vbext_ct_stdmodule Then
FName = ActiveWorkbook.Path & "\" & x.Name & ".std"
End if
If x.Type = vbext_ct_classmodule Then
FName = ActiveWorkbook.Path & "\" & x.Name & ".cls"
End if
x.Export FName
'delete old modules
VBComps.Remove x
VBComps.Import FName
Kill FName
Next
End Sub
[PS: I have gone to this trouble of trying to fashion a code cleaner
that can be incorporated into my project and distibuted as a component
part.]
--
davidm
------------------------------------------------------------------------
davidm's Profile:
http://www.excelforum.com/member.php...o&userid=20645
View this thread: http://www.excelforum.com/showthread...hreadid=478808