Excel - Export VBA Code to Text File

Asked By mark ivanowski on 07-Nov-09 01:41 PM
I am new in VBA and need to find a way to export all modules to text file.
Any suggestion that works? I have been trying some samples... they might work
but theres no clear instruction on how to setup it in step by step.

JLGWhiz replied on 07-Nov-09 01:50 PM
I usually just copy mine to either MS Word or Notepad.  Never bothered with
doing it by code.
Chip Pearson replied on 07-Nov-09 02:35 PM
A simple piece of code will do it.

Sub ExportMod()
Dim ModName As String
Dim FName As Variant
ModName = "Module1"
FName = Application.GetSaveAsFilename( _
filefilter:="Module Files (*.bas),*.bas", _
If FName = False Then
' getfilename cancelled
Exit Sub
On Error GoTo NoDelete:
Kill FName
End If
ThisWorkbook.VBProject.VBComponents(ModName).Export FName
Exit Sub
MsgBox "The file '" & Filename & "' cannot be deleted." & vbCrLf &
End Sub


ModName = "Module1"

to the name of the module you want to export. The code will prompt you
for the SaveAs file name. If you cancel this dialog, the code
terminates. If the SaveAs filename exists, the code deletes it. If
this delete operation is not successful, the module is not exported
and the code throws up a MsgBox indicating the error.

See http://www.cpearson.com/Excel/VBE.aspx for lots more info about
working with the VBA editor via code.

Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2010
Pearson Software Consulting, LLC
(email on web site)
FSt1 replied on 07-Nov-09 04:07 PM
see this site.

you are looking for the "export vb component to a text file" part.

Jon Peltier replied on 07-Nov-09 04:55 PM
Rob Bovey's Code Cleaner utility (free from http://appspro.com) exports,
deletes, then imports code modules in order to clean up those nasty VB

One of its options is to simply export all modules. When exported, the
modules are essentially test files.

- Jon
Jon Peltier
Peltier Technical Services, Inc.