What is the quickest way in Excel 2007 to export all modules?

[Guest]

I would like to find a way to reduce the amount of time it takes to export
all the VBA source code from a spreadsheet to a text file in Excel 2007.
Currently, I click on each module in turn, then go to File, Export, etc.

 

[Bob Phillips]

Get hold of a copy of Rob Bovey's indispensable Code Cleaner utility,
http://www.appspro.com/Utilities/CodeCleaner.htm

Although he calls it CodeCleaner, as it works by exporting all modules and
re-importing them, Rob provide an option to just export.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[RB Smissaert]

This works in XL 2003. It needs a reference to the Extensibility library.
You could get rid of all the GetDirectory code if you hard-code the folder
to save the files to.

Option Explicit
Private Type BROWSEINFO
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszTitle As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type
Private Declare Function SHGetPathFromIDList Lib "shell32.dll" _
Alias "SHGetPathFromIDListA" _
(ByVal pidl As Long, _
ByVal pszPath As String) As
Long
Private Declare Function SHBrowseForFolder Lib "shell32.dll" _
Alias "SHBrowseForFolderA" _
(lpBrowseInfo As BROWSEINFO) As
Long

Function GetDirectory(Optional strTitle As String = "") As String

Dim bInfo As BROWSEINFO
Dim Path As String
Dim r As Long
Dim x As Long
Dim pos As Integer

'Root folder (&H0 for Desktop, &H11 for My Computer)
bInfo.pidlRoot = &H0

'Title in the dialog
If Len(strTitle) = 0 Then
bInfo.lpszTitle = "Select a folder."
Else
bInfo.lpszTitle = strTitle
End If

'Type of directory to return
bInfo.ulFlags = &H1

'Display the dialog
x = SHBrowseForFolder(bInfo)

'Parse the result
Path = Space$(512)
r = SHGetPathFromIDList(ByVal x, ByVal Path)
If r Then
pos = InStr(Path, Chr$(0))
GetDirectory = Left(Path, pos - 1)
Else
GetDirectory = ""
End If

End Function

Sub ExportAllVBA()

Dim VBProj As VBProject
Dim VBProjToExport As VBProject
Dim VBComp As VBIDE.VBComponent
Dim Sfx As String
Dim strFolder As String
Dim strFile As String

For Each VBProj In Application.VBE.VBProjects
On Error Resume Next
Select Case MsgBox("Export all modules of this project?", _
vbYesNoCancel + vbDefaultButton2, _
VBProj.Filename)
Case vbYes
Set VBProjToExport = VBProj
Exit For
Case vbNo
Case vbCancel
Exit Sub
End Select
Next

If VBProjToExport Is Nothing Then
Exit Sub
End If

'so overwrite old files without warning
'--------------------------------------
Application.DisplayAlerts = False

strFolder = GetDirectory("pick a folder to export the modules") & "\"

For Each VBComp In VBProjToExport.VBComponents
Select Case VBComp.Type
Case vbext_ct_ClassModule, vbext_ct_Document
Sfx = ".cls"
Case vbext_ct_MSForm
Sfx = ".frm"
Case vbext_ct_StdModule
Sfx = ".bas"
Case Else
Sfx = ""
End Select
If Sfx <> "" Then
Application.StatusBar = " Exporting to " & _
strFolder & _
VBComp.Name & Sfx
VBComp.Export _
Filename:=strFolder & VBComp.Name & Sfx
End If
Next VBComp

With Application
.StatusBar = False
.ScreenUpdating = True
End With

End Sub


RBS