Exporting Module to Text File

[ExcelMonkey]

I know from Chip Pearsons site that you can export a code module to a text
file:
http://www.cpearson.com/excel/vbe.aspx

When you do so, you use the Export method of the VBComponent (see below).
My question is: Is this exporting it in its .cls/.bas/.frm format or as an
actual text file per se?

When I do it, the exported file does not show a file extension and I have to
chose what program to open it in.

Dim VBComp As VBIDE.VBComponent
VBComp.Export FileName:=FName

Thanks

EM

 

[Peter T]

Sub ExportProject()
Dim sPath As String, FName As String, sExt As String
Dim VBComp As Object ' VBIDE.VBComponent
Dim vbp As Object ' VBProject

Set vbp = ActiveWorkbook.VBProject
For Each VBComp In vbp.VBComponents

Select Case VBComp.Type
Case 1: sExt = ".bas"
Case 2, 100: sExt = ".cls"
Case 3: sExt = ".bas"
End Select

FName = sPath & VBComp.Name & sExt
VBComp.Export Filename:=FName
Next

End Sub

You can open an exported code module in a text editor, as indeed you can
almost any file. Note a code module will contain additional text not visible
in the VBE.

Regards,
Peter T

 

[ExcelMonkey]

Where I am going with this is I want to eventually import the code to a text
box in a form. I was assuming I would take an intermediary step and export
the module to a text file, then import the text file back to a textbox. I am
not planning on viewing via a text reader. As such, having the code in
cls/.bas/.frm file formats that can be opened in a text reader is not
meaningful to me.

What is the best way to undertake all of this? Can these be copied to a
clipboard?

Thanks for you response.

EM

 

[Peter T]

Sub test()
Dim vbP As Object ' VBProject
Dim vbC As Object ' VBComponent
Dim vbMod As Object ' CodeModule

Dim i As Long, j As Long
Dim sText As String, sPart As String
Dim shp As Shape

Set vbP = ActiveWorkbook.VBProject
Set vbC = vbP.VBComponents("Module1")
Set vbMod = vbC.CodeModule

sText = vbMod.Lines(1, vbMod.CountOfLines)
sText = Replace(sText, vbCrLf, vbLf)

Set shp = ActiveSheet.Shapes.AddTextbox(1, 9#, 9#, 450#, 210#)

With shp
j = 1
Do While j < Len(sText)
sPart = VBA.Strings.Mid(sText, j, 250)
.TextFrame.Characters(j).Insert String:=sPart
j = j + 250
Loop
.DrawingObject.AutoSize = True
End With

End Sub

Regards,
Peter T

 

[ExcelMonkey]

I am getting a 1004 error on the line:

.TextFrame.Characters(j).Insert String:=sPart

This occurs when j = 1. There is clearly text in sPart. What is causing the
error?

Thanks

EM

 

[Chip Pearson]

Modules are stored in plain text, with exception of the FRX files
corresponding to UserForm. The FRX is binary data (whose format is not
publicly published as far as I know) and is referenced by the FRM file
as a binary large object, or BLOB.

You're probably better reading the code out of a CodeModule object
rather than from a text file, for a couple reasons. First, the
CodeModule will let you easily find the start line and count of lines
for a procedure. If you were to read in a text file, you'd have to do
all the parsing yourself. It isn't rocket science, but if you don't
have to do it, why do it? Also, the exported text file contains
Attribute statements that are used by the VBA compiler but are not
visible in the VBA IDE. You'd have to add the logic to handle
Attributes to the parsing logic. Not that is wildly difficult, but
then again, why do it?

If you want to put the code in an OLE TextBox on a worksheet, use code
like the following:

Sub AAA()
Dim CodeMod As VBIDE.CodeModule
Dim SL As Long
Dim LC As Long
Dim S As String
Dim WS As Worksheet
Dim TBX As MSForms.TextBox
Set WS = Sheet1

Set TBX = WS.OLEObjects("TextBox1").Object
Set CodeMod =
ThisWorkbook.VBProject.VBComponents("Module1").CodeModule
SL = CodeMod.ProcStartLine("MyCode", vbext_pk_Proc)
LC = CodeMod.ProcCountLines("MyCode", vbext_pk_Proc)

With TBX
.MultiLine = True
.WordWrap = True
.Text = CodeMod.Lines(SL, LC)
End With
End Sub

Make the obvious changes.

For a TextBox on a userform, use code like the following:

Dim CodeMod As VBIDE.CodeModule
Dim SL As Long
Dim LC As Long
Dim S As String

Set CodeMod = _
ThisWorkbook.VBProject.VBComponents("module1").CodeModule
SL = CodeMod.ProcStartLine("MyCode", vbext_pk_Proc)
LC = CodeMod.ProcCountLines("MyCode", vbext_pk_Proc)
S = CodeMod.Lines(SL, LC)
Me.TextBox1.WordWrap = True
Me.TextBox1.MultiLine = True
Me.TextBox1.Text = S

This is basically the same thing as the previous code block.


Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Peter T]

No idea why it's failing for you, works fine for me. Why not dump the text
to cells


Sub test2()
Dim vbP As Object ' VBProject
Dim vbC As Object ' VBComponent
Dim vbMod As Object ' CodeModule
Dim i As Long
Dim arr
Dim sText As String

Set vbP = ActiveWorkbook.VBProject
Set vbC = vbP.VBComponents("mod_Install")
Set vbMod = vbC.CodeModule

sText = vbMod.Lines(1, vbMod.CountOfLines)
sText = Replace(sText, vbCrLf, vbLf)

arr = Split(sText, vbLf)

ReDim arr2D(1 To UBound(arr) + 1, 1 To 1) As String

For i = 0 To UBound(arr)
arr2D(i + 1, 1) = arr(i)
Next

Range("A1").Resize(UBound(arr2D)).Value = arr2D

End Sub

Regards,
Peter T

 

[ExcelMonkey]

Chip quick question. The code below passes the code text to the variable S

SL = CodeMod.ProcStartLine("MyCode", vbext_pk_Proc)
LC = CodeMod.ProcCountLines("MyCode", vbext_pk_Proc)
S = CodeMod.Lines(SL, LC)
Me.TextBox1.Text = S

This passess all the code except the first line (i.e. Sub MyCode() ). I
noticed that CodeMod.ProcStartLine("MyCode", vbext_pk_Proc) = 1. I tried
editing by put a "-1" after it but this does not pick up the first line.

Thanks

EM

 

[Chip Pearson]

It works as advertised for me. It also picks up any comments that
appear before the proc declaration. Are you sure the text box isn't
just scrolled down below the first line?

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[ExcelMonkey]

Sorry I had did not have the scroll on the form and yes the first line was
hidden and code captures everything.

Thanks

EM

 

[ExcelMonkey]

Chip I agree with all you comments regarding the extra work needed to parse a
text file.

However I may want to store the text in each module in its own text file for
other purposes. Upon doing so, I may then need to call upon them later and
thus parse these text files (inclusing Attribute stmts).

Could you provide me with a simple example as to how I would export the text
of each module to a text file and a primer on what to look for when
attempting to parse the lines and the Attribute stmts.

Thanks again for you interest.

EM

 

[ExcelMonkey]

Here is my first stab at it Chip. It assumes I am saving the code from
Module1 in the currently open workbook to a text file. What it is currently
missing is any parsing required for the Attribute stmts you noted. Also,
this assumes that the user wants all the code in the code module. I may want
to edit this allow the user to pick a particular sub/function within the code
module. This will imply that I need to parse out specific subs/function
within the text file. I am assuming I will need to loop through the lines
and find the "Sub XYZ(" or "Function XYZ(", find their associated end stmts
"End Sub" or "End Function" and only load this portion of the code into the
textbox. Happy to hear your thoughts.

'This exports to a text file and then reimports from
'the text file to a textbox
Sub AAA()
Dim CodeMod As VBIDE.CodeModule
Dim ModuleName As String
Dim SL As Long
Dim LC As Long
Dim S As String
Dim WS As Worksheet
Dim TBX As MSForms.TextBox
Dim sFileName As String
Dim sData As String

ModuleName = "Module1"

Set CodeMod = _
ThisWorkbook.VBProject.VBComponents(ModuleName).CodeModule
SL = CodeMod.ProcStartLine("ABC", vbext_pk_Proc)
LC = CodeMod.ProcCountLines("ABC", vbext_pk_Proc)
S = CodeMod.Lines(SL, LC)

Set fs = CreateObject("Scripting.FileSystemObject")

sFileName = "C:\" & ModuleName & ".txt"

Set a = fs.CreateTextFile(sFileName, True)

a.WriteLine (S)

UserForm1.TextBox1.WordWrap = True
UserForm1.TextBox1.MultiLine = True

'Import directly from Text File
Open sFileName For Input As #1
While Not EOF(1)
Line Input #1, sData
UserForm1.TextBox1.Text = UserForm1.TextBox1.Text & sData & vbCrLf
Wend

UserForm1.Show
End Sub

Thanks

EM