The code below which mimics standard CODE CLEANERS (1) exports al
(non-sheet based) modules in WORKBOOK("TEST.XLA") as text files etc.
(2) deletes the modules and (3) then imports back the files as ne
modules (The text files in folder are also meant to be deleted). Th
whole idea is to clean up the codes in the WORKBOOK so to reduce th
file size which gets bloated over time. The problem I face is that th
code works fine except that when there are Userforms, the copies create
in the directory folder fail to be removed by the *Kill filename
command. Notice that, for any one Userform, two (2) FRX files ar
created even where one file extension (.frm) is used. I woul
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 cleane
that can be incorporated into my project and distibuted as a componen
part.

--
david
-----------------------------------------------------------------------
davidm's Profile: http://www.excelforum.com/member.php...fo&userid=2064
View this thread: http://www.excelforum.com/showthread.php?threadid=47880

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