running a macro saved as a string

[jfp]

I have an Access app that manipulates a spreadsheet. The spreadsheet
can be in one of several pre-defined formats. For each, i have recorded
in Excel a macro that performs the necessary re-formatting. I have
stored these as strings in a table. I want to:
1) based on user choice, create a spreadsheet in one of the possible
formats,
2) open it and then play the appropriate macro

My VBA code (in Access) looks like this:

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet

Set xlApp = New Excel.Application
Set xlBook = xlApp.Workbooks.Open( ... Name of spreadsheet ... )

Now, if the text of the macro is in a string variable (call it
strMacro), what do i call in the Excel object model to execute it ?

 

[KM]

Try

Eval(xlApp.PublicSub)

 

[jfp]

No -- not the issue.
I have the macro as a saved string -- example:

Rows("1:1").Select
Selection.Font.Bold = True

Range("C1").Select
ActiveCell.FormulaR1C1 = "'Status"
Range("D1").Select
ActiveCell.FormulaR1C1 = "'Activity #"

Range("B1,F1,G1,H1,I1,J1,K1,L1").Select
Range("L1").Activate
Selection.HorizontalAlignment = xlRight

This string is stored in a variable in the Access app.
It is NOT in some sub in the spreadsheet.
How do i pass it to Excel for execution?
-=-=-=

 

[Tom Ogilvy]

http://www.cpearson.com/excel/vbe.htm

 

[jfp]

http://www.cpearson.com/excel/vbe.htm

OK -- thanks.
I skimmed through this; i need to create a module in the spreadsheet,
paste the saved macro string into it, and then run the macro.
Is there a shorter/simpler way?
This is a "one-time" macro -- it reformats certain cells / column widths
etc. to make the spreadsheet more legible than that created by Access.
There is no need to save it in the spreadsheet. Seems like the ability
to build a string containing some commands and then execute it directly
would be a nice feature in a language this powerful ...

 

[Tom Ogilvy]

The evaluate command cited earlier is supported in VBscript I believe, but
not VBA. I don't know how robust it is in terms of executing your strings.

Personally, if you already have the commands written out, put them in subs
in Access and use a case statement to execute the appropriate sub.

I suspect the capability doesn't exist because there appeared to be little
need for it.