One way of moving Oracle report values into a Microsoft Excel file is to use the DDE.POKE function. SearchOracle.com expert Carol Francum offers an example of a DDE.POKE sequence to a SearchOracle.com reader. Click to view the original question and response. Also see MetaLink notes 37660.1 and 1018631.6 for more information on this topic.
The following is a sample trigger for using DDE to access an MS Excel spreadsheet.
/* Declare variables */
DECLARE
appid PLS_INTEGER;
convid PLS_INTEGER;
docid PLS_INTEGER;
conv_established BOOLEAN := FALSE;
buffer CHAR(100); /* This determines the maximum size of
of the values being returned from
Excel. Adjust it if your values exceed
this. */
/* Begin trigger DDE sample */
BEGIN
/* Start Excel */
/* This line assumes that Excel is in the directory e:msofficeexcel */
APPID := DDE.APP_BEGIN('E:MSOFFICEexcelexcel.EXE',
DDE.APP_MODE_MINIMIZED);
/* Establish a conversation with Excel */
/* The following loop will not end until a conversation with Excel
has been established. Therefore, it can result in a endless loop,
so use with caution. */
WHILE NOT conv_established LOOP
BEGIN
convid := DDE.INITIATE('excel', 'system');
conv_established := TRUE;
EXCEPTION
WHEN DDE.DMLERR_NO_CONV_ESTABLISHED THEN
conv_established := FALSE;
END; -- loop
END LOOP;
/* Open Excel document */
/* This assumes that you have an Excel spreadsheet named ddetest.xls
in the root of e: */
DDE.EXECUTE(convid, '[Open("E:ddeTEST.xls")]', 10000);
/* Initiate conversation with Excel document */
docid := DDE.INITIATE('excel', 'E:ddetest.xls');
/* Begin transfer to Excel */
DDE.POKE(docid, 'R2C2', 'Text', DDE.CF_TEXT, 10000);
/* Begin transfer from Excel to Forms */
DDE.REQUEST (docid, 'R2C2:R2C3', buffer, DDE.CF_TEXT, 1000);
/* Set a text item equal to the value returned from the buffer */
:TEXT_ITEM := buffer;
/*End transfer to Excel */
DDE.TERMINATE(docid);
DDE.TERMINATE(convid);
DDE.APP_END(appid);
/* Handle exceptions */
EXCEPTION
WHEN DDE.DDE_APP_FAILURE THEN
MESSAGE('WINDOWS APPLICATION CANNOT START.');
WHEN DDE.DDE_PARAM_ERR THEN
MESSAGE('A NULL VALUE WAS PASSED TO DDE');
WHEN DDE.DMLERR_NO_CONV_ESTABLISHED THEN
MESSAGE('DDE CANNOT ESTABLISH A CONVERSATION');
WHEN DDE.DMLERR_NOTPROCESSED THEN
MESSAGE('A TRANSACTION FAILED');
/* End of trigger */
END;
Creating a default form
-----------------------
1. Startup Oracle Forms Designer, and create a default form.
2. Add a button using the Layout Editor.
3. Create a When-Button-Pressed trigger. Add the code listed in this document in the trigger.
4. Compile the trigger.
5. Add a text item named Text_item. This will display the value requested from Excel.
6. Save and generate the form.
Creating an MS Excel spreadsheet
-------------------------------
Once you have successfully generated the form, you need to create an Excel spreadsheet.
1. Start up MS Excel.
2. Add the text and formatting to the spreadsheet.
3. Save the new spreadsheet with the name ddetest.xls.
You are now ready to run the DDE sample form. Run the form and engage the trigger by pressing on the button. This should start up MS Excel and transfer the data from Oracle Forms to MS Excel and back to Oracle Forms text item.
If you have access to MetaLink, check out Notes 37660.1 and 1018631.6.
For More Information:
Check out other expert responses from Carol Francum.
See the E-Business Suite Best Web Links for resources from around the Web.
