Execute Code from a Memo Field

  • Thread starter Thread starter Geoff
  • Start date Start date
G

Geoff

I have a function that sends MS Access query output to create a MS Excel
spreadsheet and then formats the Sheet with the following commands. I would
like to be able to run the code for these formating options from a memo
field (preferably) from within this function. This way I can store the
formatting code in a memo field and format the spreadsheet from the memo
field contents.

Is this possible? Is there a better way?

Code like this.

MTExcel.Parent.Columns("A:A").ColumnWidth = 13.14
MTExcel.Parent.Columns("A:A").ColumnWidth = 18.71
MTExcel.Parent.Columns("B:B").ColumnWidth = 14.43
MTExcel.Parent.Columns("B:B").ColumnWidth = 10.86
etc...
 
Hi Geoff,

VBA doesn't do macro substitution, except to the limited extent offered
by the Eval() function. This can call functions that take parameters and
have side-effects, but it can't execute arbitrary code the way you can
in (say) Perl.

One possibility would be to build a scripting engine into your
application; I think this is pretty simple thanks to the Windows Script
Host. In that case you'd build a string containing the script (which
itself would consist of standard initialisation and termination code
with the contents of the memo field in between). ALternatively you could
write the script to disk and then fire up an external scripting engine
such as VBScript.

I'd take a more structured approach, however: have a table something
like this

tblFormattingActions
ID Autonumber PK
ParentID FK into the main table
Action Text, e.g. "SetColumnWidth"
Range Text: e.g. "A:A"
Value Text: e.g. "13.14"

and then open a recordset containing the FormattingActions for the
current record in the main table and iterate through it using something
like this to take appropriate actions:

With rstFormattingActions
Select Case .Fields("Action").Value
Case "SetColumnWidth"
MTEExcel.Parent.Columns(.Fields("Range").Value). _
ColumnWidth = .Fields("Value").Value
Case "SetFontName"
....
 
Back
Top