VBA text file to .xls

[ggrothendieck]

Suppose I were to create a text file containing VBA using
some text processing program (not connected with VBA, Excel,
etc.). The VBA program that is output, when run in Excel
will create a spreadsheet data and all. This would be done
on a regular basis, i.e. its not a one time item.

What is the easiest procedure to tell a user in order to give
them the VBA text file so that they can read it in and run it.
They are not necessarily sophisticated so its preferable
if they have minimal effort to do this. Also note that the
VBA text file may be long.

What I really want is something like
File | Open | select the VBA text file
and it gets read in and executed but I assume that that would
not work.

One other possibility is that to produce a procedure to
automatically read in the text file, execute the read-in VBA
program and then save it as an .xls file (without the VBA
program that generated it). In this case one could just
give the .xls file to the user. Since this is all done on
a regular basis this reading in and saving would have to be
done without manual intervention.

 

[Dave Peterson]

Personally, I think you'd be better served to create a single .xls file (you do
the importing of the text file into the project). Then you can share that
workbook with the end users.

Chip Pearson has some code that shows how to do this import:
http://www.cpearson.com/excel/vbe.htm

(I think the extra minutes you spend doing the work once will pay for itself in
time saved from not having to answer questions about why something didn't work.)

 

[ggrothendieck]

Thanks. I am sure you are right about the desirability of providing
an .xls rather than .vba file.

This seems like it might be a common requirement since it effectively
allows one to create an Excel spreadsheet from any language that can
output text, so I was wondering if such a utility already exists?
That is, it would be called from the command line like this:

vba2xls abc.vba abc.xls

where abc.vba is a text file holding vba code that will construct a
spreadsheet and abc.xls is the name to give the xls file that is so
constructed. This utility would automatically run Excel, read in
abc.vba, run the code it just read in and finally save the result
to abc.xls (and preferably not save the abc.vba code which
generated it).

 

[STEVE BELL]

Something from Chip Pearson: http://www.cpearson.com/excel/vbe.htm

Exporting All Modules In A Project
The procedure below will list export all of the modules in a workbook to
text files. It will save the files in the same folder as the workbook. This
can be useful for saving a backup copy of your VBA, or for transferring VBA
code from one project to another.

Sub ExportAllVBA()
Dim VBComp As VBIDE.VBComponent
Dim Sfx As String

For Each VBComp In ActiveWorkbook.VBProject.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
VBComp.Export _
Filename:=ActiveWorkbook.Path & "\" & VBComp.Name & Sfx
End If
Next VBComp
End Sub

 

[Dave Peterson]

Steve Bell posted the opposite of what you want. He saves the modules in a .xls
workbook to text files. (You want to import the text files, right?)

But that link that Steve gave has code that can import text files into a
workbook's project.

I don't think that this is common at all, but that depends on your definition of
common, I guess.

I took some code from Chip's site and modified it slightly.

Option Explicit
Sub CopyOneModule()
Dim wkbk As Workbook
Dim FName As String
Set wkbk = Workbooks.Add(1)
FName = "c:\module1.bas"
wkbk.VBProject.VBComponents.Import FName
Application.DisplayAlerts = False
wkbk.SaveAs Filename:="C:\myfile.xls", FileFormat:=xlWorkbookNormal
wkbk.Close savechanges:=False
End Sub

This creates a new workbook, imports a text file and saves the workbook.

So the code isn't that difficult.

But one of the reasons I recommend you doing it once instead of everyone do it
once is that there are things that can go wrong.

One security level that was added in xl2002 can stop this kind of code from
running. This is a user by user setting.

And why bother asking some unsophisticated users to do this? I would think that
if you're the developer, you should be doing the heavy lifting. So either
you'll have to do the work or be prepared to answer questions why it doesn't
work.

(Heck, maybe you could have the thing that creates the text file create the .xls
file, too???)

ps. Check Chip's site for lots more things you can do in the VBE.

 

[ggrothendieck]

Thanks. I'll have a look.