OraFAQ Forum: Forms » Writing to excel using forms 10g, Apps setup

Home » Developer & Programmer » Forms » Writing to excel using forms 10g, Apps setup

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Writing to excel using forms 10g, Apps setup [message #429659]

Thu, 05 November 2009 01:30

nathran
Messages: 6
Registered: January 2009

Junior Member

Hi ,
I am using a forms in 6i, to call a excel which consists of a macro. In this i am writing some values into excel sheet and finally read values from excel to show in form.
This works fine in forms 6i. Now we have to migrate all our forms to 10g. When i deploy this in application server it is not working.System is hanging in establishing CONV_ID part.
All bat files, and excel file i have stored in the forms executables path.
I feel that i have to use this with OLE2. package.
Anyone know how to change this to work in forms 10g, in apps environmnet pls help me. I am in a real soup.

many thanks.

========================================================================

PROCEDURE excel_illustration (PARAMETERS )IS

--<Declaration part>

BEGIN

tool_env.getvar('EXCEL_PATH',xls_path);

open cr_get_excel_name;
fetch cr_get_excel_name into lv_file_name;
close cr_get_excel_name ;

open get_file_path;
fetch get_file_path into lv_file_path;
close get_file_path ;

filename := lv_file_path||substr(lv_file_name,1,(instr(lv_file_name,'.')-1))||P_QOUTATION_ID||'UW'||nvl(:parameter.tp_uw_flag,'N')||'.xls';
filename_print := lv_file_path||substr(lv_file_name,1,(instr(lv_file_name,'.')-1))||P_QOUTATION_ID||'UW'||nvl(:parameter.tp_uw_flag,'N')||'.mht';

the_command := lv_file_path||'copyxls.bat'||' '||lv_file_path||lv_file_name;

Host( the_command||' '||filename,NO_SCREEN );

app_id := dde.app_begin(xls_path||'\Excel.exe '||filename, dde.app_mode_minimized);

WHILE NOT conv_established LOOP
message('0.7...');pause;
BEGIN
message('0.8...');pause;
conv_id := DDE.INITIATE('excel',filename);
conv_established := TRUE;
message('conv_id...'||conv_id);pause;
EXCEPTION

WHEN DDE.DMLERR_NO_CONV_ESTABLISHED THEN
conv_established := FALSE;
message('0.9...');pause;
END;
END LOOP;

--Writing to excel
dde.poke(Conv_ID, 'R4C2',nvl(rw_cust_details.v_title_code,' '),DDE.CF_TEXT,ln_timeout);
dde.poke(Conv_ID, 'R5C2',nvl(rw_cust_details.v_first_name,' '),DDE.CF_TEXT,ln_timeout);

--Call the Macro in Excel
dde.execute(Conv_ID, '[run("Worksheet_Calculate")]', ln_timeout);

--<Writing into Excel Ends>

--<Reading from Excel Starts>

--Reading MIV Value
BUFF := null;
CROWS := 'R18C2';
DDE.Request(conv_id,crows,Buff,DDE.CF_TEXT,1000);
BUFF := SUBSTR(BUFF,1,length(buff)-2);

IF substr(BUFF,1) ='*' THEN
BUFF := LTRIM(SUBSTR(BUFF,1,100));
END IF;

position := INSTR(BUFF,'|');

IF position <> 0 THEN
BUFF := SUBSTR(BUFF,position+1);
END IF;

BUFF := Replace(BUFF,',','');
COPY(BUFF,'NB_CONTROL.NB_EXCEL_MIV_VALUE');
--<Reading from Excel ends>

dde.execute(Conv_ID, '[run("ChangeSheet2")]', ln_timeout);

If Nvl(p_print,'N') = 'Y' Then
dde.execute(Conv_ID, '[run("MacroSaveAsMHT")]', ln_timeout);
End If;

dde.execute(Conv_ID, '[run("closeExcel")]', ln_timeout);

Host( lv_file_path||'RemoveXLS.bat '||filename,NO_SCREEN );

P_MIV_VALUE := :NB_CONTROL.NB_EXCEL_MIV_VALUE;
P_CONTRIBUTION := :NB_CONTROL.NB_EXCEL_CNTR_VALUE;
P_FNAME_PRINT := filename_print;

--Print the letter
If Nvl(p_print,'N') = 'Y' Then
dir :='C:\PROGRA~1\INTERN~1\iexplore.exe ';
Host(dir|| filename_print);
end if;

exception
when form_trigger_failure then
raise form_trigger_failure;
when others then
SET_APPLICATION_PROPERTY(CURSOR_STYLE,'DEFAULT');
message(sqlerrm);--pause;

end;

Report message to a moderator