include VBA macros as text file

[Suresh]

Hi there,

I have a strange requirement. I am not sure if it is feasible. I would be
very greatful if someone could provide a solution for this problem.

I have to generate a file. This file would have a "command" or "code" in the
first cell A1. When the user opens the file, the "Auto_Open" macro within
the file would check this code and expand it as required.

Now the problem is that, the file has to be editable by another program,
which would modify the cell A1 and insert the correct code. But this program
could work only with text files.

I am aware that excel can process XML, CSV files (which are text file) and
read in the data.

My question is, is it somehow possible to include macros in XML file ? Or is
there a better way to acheive this ?


Thanks a lot in advance.

 

[Kletcho]

Suresh,

If I understand your request, you want another program besides excel to
create a text file that is a VBA macro. An option for you may be to
use VB script. VB script is like VBA but a little less function and it
allows you to control excel from outside of excel. Here is an example
to get you started:

dim ExcelApp, ExcelWB, ExcelWS
set ExcelApp = createobject("Excel.Application")
set ExcelWB = ExcelApp.Workbooks.Open("C:\MyFile.xls")
set ExcelWS = ExcelWB.Worksheets("MyWorksheet")
ExcelWB.Range("A1") = "Some value"
set ExcelWS = Nothing
set ExcelWB = Nothing
ExcelApp.Close

This is all untested. Save the file as a .vbs file and run. Hope
that's what you were looking for.

 

[Suresh]

Suresh,

If I understand your request, you want another program besides excel to
create a text file that is a VBA macro. An option for you may be to
use VB script. VB script is like VBA but a little less function and it
allows you to control excel from outside of excel. Here is an example
to get you started:

dim ExcelApp, ExcelWB, ExcelWS
set ExcelApp = createobject("Excel.Application")
set ExcelWB = ExcelApp.Workbooks.Open("C:\MyFile.xls")
set ExcelWS = ExcelWB.Worksheets("MyWorksheet")
ExcelWB.Range("A1") = "Some value"
set ExcelWS = Nothing
set ExcelWB = Nothing
ExcelApp.Close

This is all untested. Save the file as a .vbs file and run. Hope
that's what you were looking for.

Thankyou very much.

Thats exactly what I was looking for.

hmm ... that means .... I will have to learn some VB Scripting now .. :-S

 

[Guest]

Yes you can include Excel macros stored in a text file at runtime: e.g.

application.VBE.ActiveVBProject.VBComponents.Item("ThisWorkbook").CodeModule.AddFromFile "c:\myxl.txt"

This adds all the code from c:\myxl.txt into the ThisWorkbook class/module.
Thereafter, you can call the macros as you would normally.

PROBLEM:

Once added, the code becomes permanent as soon as the workbook is saved,
unless you remove the code. Is is Chip Pearson's site that gives details of
how this is done? Someone with better memory will guide you.

 

[RB Smissaert]

VBA can import code from a text file like this:

Sub test()

Dim VBProj As VBProject
Dim VBComp As VBComponent
Dim VBCodeMod As CodeModule
Dim strFile As String

Set VBProj = ThisWorkbook.VBProject
Set VBComp = VBProj.VBComponents("SubsFromFile")
Set VBCodeMod = VBComp.CodeModule
strFile = "C:\codetester.txt"

VBCodeMod.AddFromFile strFile

End Sub

But because you alter the VBE the workbook will need to
re-compile.
It will be much simpler to read a text file, but don't import
it in the VBE. Just get the needed data from that file and
run a Sub according to that data. The other program can
edit the text file.

RBS