Question

How to call Microsoft Excel from Oracle Reports/Forms 6i

Oracle developers, Microsoft Excel, Oracle Reports, Oracle 6i, Oracle import/export, Oracle Forms, Oracle Forms 6i, Microsoft Excel import/export, Excel import/export, Oracle 6i import/export

how to call excel from oracle report/forms 6i


plz tell me the step by step process how can we do it


my e-mailid is lightmobeen@yahoo.com

if any one know the process plz send me mail sonsist of code,me very thankful to u for this

You could use the DDE package. Dynamic Data Exchange (DDE) is a mechanism by which applications can communicate and exchange data in Windows.

In this case, you would need to use the function App_Begin. This is the syntax:

The following code can help you specifically about how to implement th DDE package.
here is an example which uses scott schema. and DEPT Table.

First take a tabular database block connected to DEPT Table. Take two buttons on the canvas. write the following codes in the when-button-pressed trigger

DECLARE

filename VARCHAR2(256);
ConvID PLS_INTEGER;
AppID PLS_INTEGER;
Buffer VARCHAR2(5);
I NUMBER(3):=1;
J NUMBER(5):=1;
V_NUMBER VARCHAR2(5);
LEN NUMBER(5);
DNAME VARCHAR2(14);
LOC VARCHAR2(13);

BEGIN
filename := GET_FILE_NAME(File_Filter=> 'EXCEL Files (*.XLS)|*.XLS|');

AppID := DDE.App_Begin('C:\Program Files\Microsoft Office\Office10\EXCEL.EXE'||' '||filename,DDE.APP_MODE_MINIMIZED);

ConvID := DDE.Initiate('EXCEL.EXE',filename);

LOOP
BEGIN
BUFFER:=NULL;
V_NUMBER:=NULL;
I:=1;
DDE.Request(ConvID,'R'||J||'C1',Buffer,DDE.CF_TEXT,1000);
DDE.Request(ConvID,'R'||J||'C2',dname,DDE.CF_TEXT,1000);
DDE.Request(ConvID,'R'||J||'C3',loc,DDE.CF_TEXT,1000);

IF ASCII(BUFFER)=13 THEN
EXIT;
END IF;

LEN:=LENGTH(Buffer);
LOOP
EXIT WHEN I>LEN;
IF SUBSTR(BUFFER,I,1) IN ('0','1','2','3','4','5','6','7','8','9') THEN
V_NUMBER:=V_NUMBER||SUBSTR(BUFFER,I,1);
I:=I+1;
ELSE
I:=I+1;
END IF;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
exit;
WHEN OTHERS THEN
MESSAGE(SQLERRM);
END;
:DEPTNO:=TO_NUMBER(V_NUMBER);
:DNAME:=DNAME;
:LOC:=LOC;
J:=J+1;
NEXT_RECORD;
END LOOP;
EXCEPTION
WHEN DDE.DDE_APP_NOT_FOUND THEN
MESSAGE('APP NOT FOUND');
WHEN DDE.DDE_APP_FAILURE THEN
MESSAGE('APP FAILED');
WHEN DDE.DDE_INIT_FAILED THEN
MESSAGE('INIT FAILED');
WHEN DDE.DMLERR_NO_CONV_ESTABLISHED THEN
MESSAGE('CLIENT FAILED TO ESTABLISH CONVERSATION');
WHEN OTHERS THEN
MESSAGE(SQLERRM);
END;

This will load Excel data on the oracle form, and after that everything is same as Oracle forms' classic performance.

Now to create an Excel file from Database In another button write the following codes.

DECLARE

CURSOR C1 IS
SELECT DEPTNO,DNAME,LOC
FROM DEPT;

filename VARCHAR2(256);
ConvID PLS_INTEGER;
AppID PLS_INTEGER;
Buffer VARCHAR2(5);
I NUMBER(3):=1;
J NUMBER(5):=1;
V_NUMBER VARCHAR2(5);
LEN NUMBER(5);
DNAME VARCHAR2(14);
LOC VARCHAR2(13);
OUT_FILE TEXT_IO.FILE_TYPE;
BEGIN
filename := GET_FILE_NAME(File_Filter=> 'EXCEL Files (*.XLS)|*.XLS|',DIALOG_TYPE=>2);

OUT_FILE:=TEXT_IO.FOPEN(filename,'W');
TEXT_IO.FCLOSE(OUT_FILE);


AppID := DDE.App_Begin('C:\Program Files\Microsoft Office\Office10\EXCEL.EXE'||' '||filename,DDE.APP_MODE_MINIMIZED);
--DDE.APP_FOCUS(APPID);
ConvID := DDE.Initiate('EXCEL.EXE',filename);
FOR R IN C1
LOOP
DDE.POKE(ConvID,'R'||J||'C1',R.DEPTNO,DDE.CF_TEXT,1000);
DDE.POKE(ConvID,'R'||J||'C2',R.DNAME,DDE.CF_TEXT,1000);
DDE.POKE(ConvID,'R'||J||'C3',R.LOC,DDE.CF_TEXT,1000);
J:=J+1;
END LOOP;
TEXT_IO.FCLOSE(OUT_FILE);

EXCEPTION
WHEN DDE.DDE_APP_NOT_FOUND THEN
MESSAGE('APP NOT FOUND');
WHEN DDE.DDE_APP_FAILURE THEN
MESSAGE('APP FAILED');
WHEN DDE.DDE_INIT_FAILED THEN
MESSAGE('INIT FAILED');
WHEN DDE.DMLERR_NO_CONV_ESTABLISHED THEN
MESSAGE('CLIENT FAILED TO ESTABLISH CONVERSATION');
WHEN OTHERS THEN
MESSAGE(SQLERRM);
END;


Try this if you have any problem in implementing the code please mail me at mamun_iuk@yahoo.com