​A selection of topics on IT and its application to finance. 
​Send me your comments, questions ​or suggestions by clicking
h​ere


elmama
Using DDE in Forms 


This issue stems from a very specific problem I had a while back trying to get Forms to supply data to an EXCEL spreadsheet. Actually loading the data into the spreadsheet didn't turn out to be too bad but the problem I had was that I then wanted to run a macro. As you can imagine that's a very powerful tool to have at your disposal and I tried everywhere to find the syntax, the Net, newsgroups, books even Oracle itself but had absolutely no luck. Eventually after much trial and error I kind of stumbled across the solution on my own and present it here along with a general discussion of DDE/Forms interaction itself.

Using DDE with Forms has probably been superseded now with OLE and Forms, however I still like the former for one main reason. Its far far easier to code. The problem isn't with the basic commands, it's getting some real life examples of how you use it in anger with WORD, EXCEL and other popular PC programs. I've only got experience with the two software packages mentioned so those are the examples I'll show here. 

EXCEL example

DECLARE
    appid pls_integer;
    convid pls_integer;
     x  number;
BEGIN
  appid :=    
    dde.app_begin('c:\program files\microsoft office\office\excel.exe
                                 c:\index_values.xls',dde.app_mode_minimized);

    convid := dde.initiate('EXCEL','c:\index_values.xls');

    /* run a pre-defined macro to, for example, clear out the sheet */
    dde.execute(convid,'[run("Macro1")]',1000);

    /* Put a title at Row 1 Column 1 */
    rowcol := 'R1C1';
    dde.poke(convid,rowcol,'Put a title here',dde.cf_text,1000);

    /* Put contents of form field var into Row 3 Column A */
    rowcol := 'R3C1';
    dde.poke(convid,rowcol,:var,dde.cf_text,1000);

    /* process a bunch of records in a block, on the spreadsheet we start putting the data in at column B, row 5 and work down the rows*/
    go_block('block0');
    go_record(1);

    x := 5;
    while :id is not null
    loop
        rowcol := 'R'||to_char(x)||'C2';
        dde.poke(convid,rowcol,:id,dde.cf_text,1000);
        x := x + 1;  

        next_record;
    end loop;

    /* Now run a final macro to, for example, chart data etc . */
    dde.execute(convid, '[run("Macro2")]',1000);


    /*  close EXCEL and clean up*/
    dde.terminate(convid);
    dde.app_end(appid);

END;


Told you it was easy didn't I, but at the time getting that '[run("Macro1")]' bit of syntax was a real bind. The 1000 that you can see everywhere is a time-out values in milli-seconds for EXCEL, WORD or whatever you're using to respond to the DDE request. Occasionally you may have to increase this figure to get things to work.

Using WORD and DDE is very similar but instead of 'poke'ing into cells you have to set up bookmarks in WORD first and poke to these. Here's a short example.

WORD  example

DECLARE
    appid pls_integer;
    convid pls_integer;
     x  number;
BEGIN

appid:=dde.app_begin('C:\Program Files\Microsoft Office\Office\WINWORD.EXE C:\FAX.DOC',dde.app_mode_normal);
   convid:=dde.initiate('WINWORD','C:\FAX.DOC');

/* Put six items of data at six different bookmarks pre-defined in WORD */

dde.poke(convid,'FAX_TO',faxdata1,dde.cf_text,1000);
dde.poke(convid,'FAX_NO',faxdata2,dde.cf_text,1000);
dde.poke(convid,'FAX_TO2',faxdata3,dde.cf_text,1000);
dde.poke(convid,'FAX_TITLE',faxdata4,dde.cf_text,1000);
dde.poke(convid,'FAX_FROM',faxdata6,dde.cf_text,1000);
dde.poke(convid,'FAX_FROM_TEL',faxdata7,dde.cf_text,1000);

/*  close WORD and clean up*/
dde.terminate(convid);
dde.app_end(appid);

END;


Warning: before using DDE with Word and EXCEL make sure you turn off macro virus protection. This can be found under the Tools->Opti
ons->General Tab. Ensure the relevant box is unchecked.