Re: Execute Code from a Memo Field

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: John Nurick (j.mapSoN.nurick_at_dial.pipex.com)
Date: 04/24/04 

Date: Sat, 24 Apr 2004 10:02:20 +0100

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"
        ....

On Fri, 23 Apr 2004 18:06:47 GMT, "Geoff" <cbsinc@earthlink.net> wrote:

>I have a function that sends MS Access query output to create a MS Excel
>spread*** and then formats the *** 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 spread*** 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...
>
>
> 

--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
Quantcast