read data from an excel-sheet using Oracle Forms

From: Roland Kopetzky <kopetzky_at_ike.uni-stuttgart.de>
Date: Mon, 11 Jan 1999 13:51:43 +0100
Message-ID: <3699F3DE.AE3ED26A@ike.uni-stuttgart.de>

Hello Professionals,

I have problems to read data from an excel-sheet using Oracle Forms. I will show you the code I use on an Pentium 266MHz with Windows NT 4.0 with service pack 3.

The code is inside a when-mouse-click trigger:

DECLARE
app_name Varchar2(60);
app_id PLS_INTEGER;
ChannelId PLS_INTEGER;
name VARCHAR2(160);
file_name VARCHAR2(100);
buffer VARCHAR2;

BEGIN
:GLOBAL.application_ID := null;
:GLOBAL.channel_id := null;
if :GLOBAL.application_id is NOT NULL then Message ('Anwendung Excel schon gestartet'); else
app_name := 'C:\PROGRA~1\MICROS~1\OFFICE\EXCEL.EXE'; file_name := 'C:\TEMP\TESTXLS.XLS';
name := app_name || ' ' ||file_name;
-- Message('Anwendung ' || app_name|| ' '||filename);
app_id := DDE.APP_BEGIN(name, DDE.APP_MODE_NORMAL); MESSAGE('APP_ID: '||to_char(app_ID));
ChannelId := DDE.INITIATE('Microsoft EXCEL', 'C:\TEMP\TESTXLS.XLS');

MESSAGE('APP_ID: '||to_char(app_ID)||' Channel_ID: '||to_char(ChannelId));
-- DDE.Poke(ChannelId, 'R1C1','TESTText', DDE.CF_TEXT, 10000);

• Message('NACH POKE :');Synchronize; DDE.Request (ChannelId, 'R1C1', buffer, DDE.CF_TEXT, 10000);
• Message('Nach REQUEST');synchronize;

Message('Excel: <'||buffer||'>');synchronize; DDE.TERMINATE (ChannelId);
DDE.APP_END (app_id);

end if;
EXCEPTION
-- when DDE.DMLERR_NOT_SUPPORTED then

• MESSAGE('DDE wird nicht unterstützt.');
• RAISE FORM_TRIGGER_FAILURE; when DDE.DDE_APP_FAILURE then MESSAGE('Konnte Anwendung für DDE nicht starten.'); RAISE FORM_TRIGGER_FAILURE; when DDE.DDE_INIT_FAILED then MESSAGE('Konnte DDE Kommunikationskanal nicht installieren.'); RAISE FORM_TRIGGER_FAILURE; when DDE.DMLERR_NO_CONV_ESTABLISHED then MESSAGE('Konnte DDE Kommunikationskanal nicht nutzen.'); RAISE FORM_TRIGGER_FAILURE; when DDE.DMLERR_DATAACKTIMEOUT then MESSAGE('Fehler: DDE.DMLERR_DATAACKTIMEOUT.'); RAISE FORM_TRIGGER_FAILURE; when DDE.DMLERR_EXECACKTIMEOUT then MESSAGE('Fehler: DDE.DMLERR_EXECACKTIMEOUT.'); RAISE FORM_TRIGGER_FAILURE; when DDE.DMLERR_POKEACKTIMEOUT then MESSAGE('Fehler: DDE.DMLERR_POKEACKTIMEOUT.'); RAISE FORM_TRIGGER_FAILURE; when DDE.DDE_PARAM_ERR then MESSAGE('Fehler: DDE.DDE_PARAM_ERR.'); RAISE FORM_TRIGGER_FAILURE; when DDE.DMLERR_INVALIDPARAMETER then MESSAGE('Fehler: DDE.DMLERR_INVALIDPARAMETER.'); RAISE FORM_TRIGGER_FAILURE; when DDE.DMLERR_NOTPROCESSED then MESSAGE('Fehler: DDE.DMLERR_NOTPROCESSED.'); DDE.TERMINATE (ChannelId); DDE.APP_END (app_id); EXIT_FORM; RAISE FORM_TRIGGER_FAILURE; when DDE.DMLERR_BUSY then MESSAGE('Fehler: DDE.DMLERR_BUSY.'); RAISE FORM_TRIGGER_FAILURE; when DDE.DMLERR_SERVER_DIED then MESSAGE('Fehler: DDE.DMLERR_SERVER_DIED.'); RAISE FORM_TRIGGER_FAILURE; when others then MESSAGE ('FEHLER: '||to_char(sqlcode)||' '||sqlerrm); RAISE FORM_TRIGGER_FAILURE; END;

The curisous thing is, that when I use Forms 5.0 I get the error 'Fehler: DDE.DMLERR_NOTPROCESSED.' which points to the dde.request statement.
Using Forms 4.5 I get the error 'Konnte DDE Kommunikationskanal nicht installieren.'
which points to the dde.initiate statement. What am I doing wrong? Are there
any properties I have to set?

Does anyone know how to solve this problem? I want to read Information like this out of Excel:

date1
header1_string header2_string header3_string textstring1 numericvalue1 numericvalue2 :
textstringn numericvalue1n numericvalue2n

date2
header1_string header2_string header3_string textstring1 numericvalue1 numericvalue2 :
textstringm numericvalue1m numericvalue2m

Is there a better idea instead of using DDE? What about OLE or ODBC for this problem? Please answer me in the newsgroup or email me to kopetzkyike.uni-stuttgart.de <mailto:tischendorf_at_arkusa.de>

Thanks a lot. Any help is welcome because I do not have other ideas how to solve this.

Roland Kopetzky
--

---

Dipl.-Ing. Roland Kopetzky
Institut f. Kernenergetik u. Energiesysteme d. Universitaet Stuttgart Abteilung Wissensverarbeitung und Numerik

Postfach - 70550 Stuttgart                         II  KK  KK  EEEEEE
Pfaffenwaldring 31, D - 70569 Stuttgart            II  KK KK   EE
                                                   II  KKKK    EEEEE
Phone: +49 711/685-2135                            II  KK KK   EE
Fax:   +49 711/685-2010                            II  KK  KK  EEEEEE

E-Mail: kopetzky_at_ike.uni-stuttgart.de
WWW: http://www.ike.uni-stuttgart.de/~www_wn/leute/kopetzky/

---

Received on Mon Jan 11 1999 - 06:51:43 CST