Home » Developer & Programmer » Forms » Writing to excel using forms 10g, Apps setup
Writing to excel using forms 10g, Apps setup [message #429659] Thu, 05 November 2009 01:30 Go to next message
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;


Re: Writing to excel using forms 10g, Apps setup [message #429675 is a reply to message #429659] Thu, 05 November 2009 03:16 Go to previous messageGo to next message
waqasbhai
Messages: 118
Registered: August 2008
Location: Pakistan
Senior Member
one way to achieve this is by using webutil
Re: Writing to excel using forms 10g, Apps setup [message #429681 is a reply to message #429675] Thu, 05 November 2009 03:39 Go to previous messageGo to next message
nathran
Messages: 6
Registered: January 2009
Junior Member
Thanks a lot for you reply.
Do you have any sample code to acheive this.

many thanks
Re: Writing to excel using forms 10g, Apps setup [message #429685 is a reply to message #429659] Thu, 05 November 2009 04:03 Go to previous message
waqasbhai
Messages: 118
Registered: August 2008
Location: Pakistan
Senior Member
you need to configure webutil before using any code. read webutil document for more and how to configure it.
Previous Topic: Parameter Problem`
Next Topic: showing LOV in Query mode
Goto Forum:
  


Current Time: Thu Dec 08 03:51:20 CST 2016

Total time taken to generate the page: 0.12816 seconds