Home » Developer & Programmer » Forms » ERROR IN OPENING EXCEL VIA WINDOWS SCHEDULAER (WINDOW SERVER 2008)
ERROR IN OPENING EXCEL VIA WINDOWS SCHEDULAER [message #660610] Wed, 22 February 2017 05:45 Go to next message
NIJ1611
Messages: 17
Registered: February 2017
Junior Member
I have facing error in opening excel file via window scheduler.
following coding are below mention..
----------------------------------------------------------------------------

procedure form_to_excel_poke is
obj ole2.obj_type;
ti text_io.file_type;
l_doc_id number;
l_appid number;
l_appid1 number;
l_records number :=0;
l_row number :=0;
l_col number :=0;
l_excel_path varchar2(200):='';
l_row_col varchar2(100):='';
i number :=0;
--Other Variables
l_data varchar2(4000):='';
l_period varchar2(1000):='';
l_col_head varchar2(100):='';
l_str varchar2(100):='';
l_datatype varchar2(100):='';
l_blockname varchar2(100):='';
l_formatmask varchar2(100):='';
l_date date;
temp varchar2(4000):='';
temp1 varchar2(4000):='';
a number ;
begin

l_row:=0;
l_col:=1;
obj := ole2.create_obj('excel.application');
l_excel_path := ole2.get_char_property(obj,'path');

--- ole2.release_obj(obj);
--- ole2.invoke(obj,'QUIT');
l_excel_path := l_excel_path||'\'||'excel.exe';
HOST('cmd.exe /c mkdir X:\MAILS',no_SCREEN);




---- temp:= substr(replace(nvl(:parameter.lhs_report,nvl(:parameter.user_report,:getprint.p_rep_head)),' ','_'),1,50)||'_'||:parameter.user_code||'_'||:parameter.tnature||'_'||to_char(sysdate,'ddmmrrhh24miss')||'.xls';
---- temp:= substr(replace(nvl(:parameter.lhs_report,nvl(:parameter.user_report,:getprint.p_rep_head)),' ','_'),1,50)||'_'||:parameter.user_code||'_'||to_char(sysdate,'ddmmrrhh24miss')||'.xls';
---- temp1 := l_excel_path||temp ;
if :parameter.AUTO_SCHDL_REPORT_FILENAME is not null then
temp1 := upper(RTRIM(LTRIM(:parameter.AUTO_SCHDL_REPORT_FILENAME))) ;
temp := substr(upper(RTRIM(LTRIM(:parameter.AUTO_SCHDL_REPORT_FILENAME))),10,length(upper(RTRIM(LTRIM(:parameter.AUTO_SCHDL_REPORT_FILENAME)) )));
l_appid1 := dde.app_begin(l_excel_path, DDE.app_mode_minimized);
else
------temp:= substr(replace(nvl(:parameter.lhs_report,nvl(:parameter.user_report,:getprint.p_rep_head)),' ','_'),1,50)||'_'||:parameter.user_code||'_'||to_char(sysdate,'ddmmrrhh24miss')||'.xls';
temp:= substr(replace(nvl(:parameter.lhs_report,nvl(:parameter.user_report,:getprint.p_rep_head)),' ','_'),1,50)||'_'||to_char(sysdate,'ddmmrrhh24miss')||'.xls';
temp1 := upper('X:\MAILS\'||temp) ;
l_appid1 := dde.app_begin(l_excel_path, DDE.app_mode_normal);
----temp:= substr(replace(nvl(:parameter.lhs_report,nvl(:parameter.user_report,:getprint.p_rep_head)),' ','_'),1,50)||'_'||:parameter.user_code||'_'||to_char(sysdate,'ddmmrrhh24miss')||'.xls';
end if;


---- l_appid := dde.app_begin(l_excel_path, DDE.app_mode_normal);
dde.app_focus(l_appid1);
l_doc_id := dde.initiate('EXCEL','system');
DDE.EXECUTE(l_doc_id, '[Save.As("'||temp1||'",1)]',10000);
l_doc_id := DDE.INITIATE('EXCEL', temp);

if l_doc_id<>-1 then
--Report Heading
i := i+1;
l_row_col:='r'||i||'c'||1;
if :getprint.list_entity_code is null then
begin
dde.poke(l_doc_id, l_row_col, 'All entities', dde.cf_text, 40000);
exception when others then
null;
end;
else
begin
dde.poke(l_doc_id, l_row_col, lhs_utility.get_name('entity_code',:list_entity_code), dde.cf_text, 40000);
exception when others then
null;
end;
end if;
if :getprint.begdate is not null and :getprint.enddate is not null then
l_period:= ' printed on :'||trunc(sysdate);
else
l_period:=null;
end if;
i := i+1;
l_row_col:='r'||i||'c'||1;
begin
dde.poke(l_doc_id, l_row_col, :getprint.p_rep_head||l_period, dde.cf_text, 40000);
exception when others then
null;
end;
if :parameter.period_str is not null then
i := i+1;
l_row_col:='r'||i||'c'||1;
begin
dde.poke(l_doc_id, l_row_col, :parameter.period_str, dde.cf_text, 40000);
exception when others then
null;
end;
end if;
if :Parameter.rep_filter is not null then
i := i+1;
l_row_col:='r'||i||'c'||1;
begin
dde.poke(l_doc_id, l_row_col, :Parameter.rep_filter, dde.cf_text, 40000);
exception when others then
null;
end;



end if;
--Report Heading Column Heading
i := i+1;
for j in 1..:parameter.l_col-1 loop
l_col_head := get_item_property('temp.col'||j,prompt_text);
l_row_col:='r'||i||'c'||j;
begin
dde.poke(l_doc_id, l_row_col, nvl(l_col_head,'#'), dde.cf_text, 40000);
exception when others then
null;
end;
end loop ;
loop
i := i+1;
for j in 1..:parameter.l_col-1 loop
l_row_col:='r'||i||'c'||j;
l_str :=upper(get_item_property('temp.COL'||j,PROMPT_TEXT));
begin
select datatype,blockname into l_datatype,l_blockname
from lhssys_engine_cols
where engine_name=:parameter.report
and ltrim(rtrim(upper(disp_column)))=ltrim(rtrim(upper(l_str)));
exception when others then
l_datatype:=null;
l_blockname:=null;
end;
l_data:=nvl(name_in('temp.col'||j),' ');
if l_blockname='TEXTDATA' then
begin
if lhs_utility.lhssys_is_number(l_data)=1 and substr(l_data,1,1)='0' then
l_data:='=TEXT('||nvl(l_data,'0')||',"'||lpad('0',length(l_data),'0')||'")';
end if;
exception when others then
null;
end;
begin
if l_datatype='F' then
l_date:=to_date(l_data,'MONRR');
if l_date is not null and (instr(l_data,'-')=0 and instr(l_data,'/')=0) then
l_data:='=UPPER(TEXT(DATEVALUE("'||l_data||'"),"MMMDD"))';
end if;
end if;
exception when others then
null;
end;
end if;
begin
dde.poke(l_doc_id, l_row_col, l_data, dde.cf_text, 40000);
exception when others then
null;
end;
end loop ;
exit when :system.last_record = 'TRUE';
next_record;
end loop;
end if;
if :parameter.AUTO_SCHDL_REPORT_FILENAME is not null then
DDE.EXECUTE(l_doc_id,'[Save()]',10000);
end if;


DDE.TERMINATE(l_doc_id);


dde.app_end(l_appid1);


exception
WHEN DDE.DDE_APP_FAILURE THEN
lhs_lib.msgbox('WINDOWS APPLICATION CANNOT START.');
WHEN DDE.DDE_PARAM_ERR THEN
lhs_lib.msgbox('A NULL VALUE WAS PASSED TO DDE');
WHEN DDE.DMLERR_NO_CONV_ESTABLISHED THEN
lhs_lib.msgbox('DDE CANNOT ESTABLISH A CONVERSATION');
WHEN DDE.DMLERR_NOTPROCESSED THEN
lhs_lib.msgbox('A TRANSACTION FAILED');

when others then
DDE.EXECUTE(l_doc_id,'[Save()]',10000);
DDE.TERMINATE(l_doc_id);
DDE.APP_END(l_appid1);
--- lhs_lib.msgbox('Error while inserting data from ERP to excel.-'||sqlerrm);
end;

Re: ERROR IN OPENING EXCEL VIA WINDOWS SCHEDULAER [message #660611 is a reply to message #660610] Wed, 22 February 2017 06:19 Go to previous messageGo to next message
NIJ1611
Messages: 17
Registered: February 2017
Junior Member
I have facing error in opening excel file via window scheduler.
following coding are below mention..
----------------------------------------------------------------------------

procedure form_to_excel_poke is
obj ole2.obj_type;
ti text_io.file_type;
l_doc_id number;
l_appid number;
l_appid1 number;
l_records number :=0;
l_row number :=0;
l_col number :=0;
l_excel_path varchar2(200):='';
l_row_col varchar2(100):='';
i number :=0;
--Other Variables
l_data varchar2(4000):='';
l_period varchar2(1000):='';
l_col_head varchar2(100):='';
l_str varchar2(100):='';
l_datatype varchar2(100):='';
l_blockname varchar2(100):='';
l_formatmask varchar2(100):='';
l_date date;
temp varchar2(4000):='';
temp1 varchar2(4000):='';
a number ;
begin

l_row:=0;
l_col:=1;
obj := ole2.create_obj('excel.application');
l_excel_path := ole2.get_char_property(obj,'path');

--- ole2.release_obj(obj);
--- ole2.invoke(obj,'QUIT');
l_excel_path := l_excel_path||'\'||'excel.exe';
HOST('cmd.exe /c mkdir X:\MAILS',no_SCREEN);




---- temp:= substr(replace(nvl(:parameter.lhs_report,nvl(:parameter.user_report,:getprint.p_rep_head)),' ','_'),1,50)||'_'||:parameter.user_code||'_'||:parameter.tnature||'_'||to_char(sysdate,'ddmmrrhh24miss')||'.xls';
---- temp:= substr(replace(nvl(:parameter.lhs_report,nvl(:parameter.user_report,:getprint.p_rep_head)),' ','_'),1,50)||'_'||:parameter.user_code||'_'||to_char(sysdate,'ddmmrrhh24miss')||'.xls';
---- temp1 := l_excel_path||temp ;
if :parameter.AUTO_SCHDL_REPORT_FILENAME is not null then
temp1 := upper(RTRIM(LTRIM(:parameter.AUTO_SCHDL_REPORT_FILENAME))) ;
temp := substr(upper(RTRIM(LTRIM(:parameter.AUTO_SCHDL_REPORT_FILENAME))),10,length(upper(RTRIM(LTRIM(:parameter.AUTO_SCHDL_REPORT_FILENAME)) )));
l_appid1 := dde.app_begin(l_excel_path, DDE.app_mode_minimized);
else
------temp:= substr(replace(nvl(:parameter.lhs_report,nvl(:parameter.user_report,:getprint.p_rep_head)),' ','_'),1,50)||'_'||:parameter.user_code||'_'||to_char(sysdate,'ddmmrrhh24miss')||'.xls';
temp:= substr(replace(nvl(:parameter.lhs_report,nvl(:parameter.user_report,:getprint.p_rep_head)),' ','_'),1,50)||'_'||to_char(sysdate,'ddmmrrhh24miss')||'.xls';
temp1 := upper('X:\MAILS\'||temp) ;
l_appid1 := dde.app_begin(l_excel_path, DDE.app_mode_normal);
----temp:= substr(replace(nvl(:parameter.lhs_report,nvl(:parameter.user_report,:getprint.p_rep_head)),' ','_'),1,50)||'_'||:parameter.user_code||'_'||to_char(sysdate,'ddmmrrhh24miss')||'.xls';
end if;


---- l_appid := dde.app_begin(l_excel_path, DDE.app_mode_normal);
dde.app_focus(l_appid1);
l_doc_id := dde.initiate('EXCEL','system');
DDE.EXECUTE(l_doc_id, '[Save.As("'||temp1||'",1)]',10000);
l_doc_id := DDE.INITIATE('EXCEL', temp);

if l_doc_id<>-1 then
--Report Heading
i := i+1;
l_row_col:='r'||i||'c'||1;
if :getprint.list_entity_code is null then
begin
dde.poke(l_doc_id, l_row_col, 'All entities', dde.cf_text, 40000);
exception when others then
null;
end;
else
begin
dde.poke(l_doc_id, l_row_col, lhs_utility.get_name('entity_code',:list_entity_code), dde.cf_text, 40000);
exception when others then
null;
end;
end if;
if :getprint.begdate is not null and :getprint.enddate is not null then
l_period:= ' printed on :'||trunc(sysdate);
else
l_period:=null;
end if;
i := i+1;
l_row_col:='r'||i||'c'||1;
begin
dde.poke(l_doc_id, l_row_col, :getprint.p_rep_head||l_period, dde.cf_text, 40000);
exception when others then
null;
end;
if :parameter.period_str is not null then
i := i+1;
l_row_col:='r'||i||'c'||1;
begin
dde.poke(l_doc_id, l_row_col, :parameter.period_str, dde.cf_text, 40000);
exception when others then
null;
end;
end if;
if :Parameter.rep_filter is not null then
i := i+1;
l_row_col:='r'||i||'c'||1;
begin
dde.poke(l_doc_id, l_row_col, :Parameter.rep_filter, dde.cf_text, 40000);
exception when others then
null;
end;



end if;
--Report Heading Column Heading
i := i+1;
for j in 1..:parameter.l_col-1 loop
l_col_head := get_item_property('temp.col'||j,prompt_text);
l_row_col:='r'||i||'c'||j;
begin
dde.poke(l_doc_id, l_row_col, nvl(l_col_head,'#'), dde.cf_text, 40000);
exception when others then
null;
end;
end loop ;
loop
i := i+1;
for j in 1..:parameter.l_col-1 loop
l_row_col:='r'||i||'c'||j;
l_str :=upper(get_item_property('temp.COL'||j,PROMPT_TEXT));
begin
select datatype,blockname into l_datatype,l_blockname
from lhssys_engine_cols
where engine_name=:parameter.report
and ltrim(rtrim(upper(disp_column)))=ltrim(rtrim(upper(l_str)));
exception when others then
l_datatype:=null;
l_blockname:=null;
end;
l_data:=nvl(name_in('temp.col'||j),' ');
if l_blockname='TEXTDATA' then
begin
if lhs_utility.lhssys_is_number(l_data)=1 and substr(l_data,1,1)='0' then
l_data:='=TEXT('||nvl(l_data,'0')||',"'||lpad('0',length(l_data),'0')||'")';
end if;
exception when others then
null;
end;
begin
if l_datatype='F' then
l_date:=to_date(l_data,'MONRR');
if l_date is not null and (instr(l_data,'-')=0 and instr(l_data,'/')=0) then
l_data:='=UPPER(TEXT(DATEVALUE("'||l_data||'"),"MMMDD"))';
end if;
end if;
exception when others then
null;
end;
end if;
begin
dde.poke(l_doc_id, l_row_col, l_data, dde.cf_text, 40000);
exception when others then
null;
end;
end loop ;
exit when :system.last_record = 'TRUE';
next_record;
end loop;
end if;
if :parameter.AUTO_SCHDL_REPORT_FILENAME is not null then
DDE.EXECUTE(l_doc_id,'[Save()]',10000);
end if;


DDE.TERMINATE(l_doc_id);


dde.app_end(l_appid1);


exception
WHEN DDE.DDE_APP_FAILURE THEN
lhs_lib.msgbox('WINDOWS APPLICATION CANNOT START.');
WHEN DDE.DDE_PARAM_ERR THEN
lhs_lib.msgbox('A NULL VALUE WAS PASSED TO DDE');
WHEN DDE.DMLERR_NO_CONV_ESTABLISHED THEN
lhs_lib.msgbox('DDE CANNOT ESTABLISH A CONVERSATION');
WHEN DDE.DMLERR_NOTPROCESSED THEN
lhs_lib.msgbox('A TRANSACTION FAILED');

when others then
DDE.EXECUTE(l_doc_id,'[Save()]',10000);
DDE.TERMINATE(l_doc_id);
DDE.APP_END(l_appid1);
--- lhs_lib.msgbox('Error while inserting data from ERP to excel.-'||sqlerrm);
end;

/forum/fa/13457/0/

[mod-edit: image inserted into message body by bb]

[Updated on: Thu, 23 February 2017 21:19] by Moderator

Report message to a moderator

Re: ERROR IN OPENING EXCEL VIA WINDOWS SCHEDULAER [message #660612 is a reply to message #660610] Wed, 22 February 2017 07:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

Re: ERROR IN OPENING EXCEL VIA WINDOWS SCHEDULAER [message #660622 is a reply to message #660610] Thu, 23 February 2017 01:30 Go to previous message
NIJ1611
Messages: 17
Registered: February 2017
Junior Member
oracle version
----------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit
PL/SQL Release 10.2.0.4.0 - Production
Previous Topic: What's next after Oracle Forms?
Next Topic: oracle forms application
Goto Forum:
  


Current Time: Thu Mar 28 06:53:42 CDT 2024