Home » Developer & Programmer » Forms » Problem with working in OLE2 for EXCEL
icon8.gif  Problem with working in OLE2 for EXCEL [message #393947] Wed, 25 March 2009 02:58 Go to next message
pinkalsolanki
Messages: 25
Registered: December 2008
Location: Surat/Gujarat
Junior Member

Hi Everybody..

I am getting a Value of First Cell and Active Sheet (Sheet1) of Excel File. Everything works great, but when i change the value or add formula in another cell (i mean make changes in excel file) and saving workbook with
' OLE2.INVOKE(workbook,'Save') ' statement, the EXCEL.EXE still running in task manager it does not kill.

If i only get value from excel then everything is ok. but i make any changes in excel and 'save' workbook , then this problem will create.


My Code given below..


----------------------------------------------------------
Declare
application OLE2.OBJ_TYPE;
workbooks OLE2.OBJ_TYPE;
workbook OLE2.OBJ_TYPE;
worksheet OLE2.OBJ_TYPE;
cell OLE2.OBJ_TYPE;
FormulaCell OLE2.OBJ_TYPE;
BlankCell OLE2.OBJ_TYPE;

args OLE2.LIST_TYPE;

vCellAddress Varchar2(100);
vFormulaResult Varchar2(100);
IsBlank Varchar2(20);
IsText Varchar2(20);
vValue Varchar2(20);
Begin

Begin
application := OLE2.CREATE_OBJ('Excel.Application');
ole2.set_property(application,'Visible','false');
workbooks := OLE2.GET_OBJ_PROPERTY(application, 'Workbooks');

args := OLE2.CREATE_ARGLIST;
ole2.add_arg(args,'C:\New.xls');
workbook := ole2.GET_OBJ_PROPERTY(workbooks,'Open',args);
ole2.destroy_arglist(args);

args := OLE2.CREATE_ARGLIST;
ole2.add_arg(args,'Sheet1');
--worksheet := ole2.GET_OBJ_PROPERTY(workbook,'Worksheets',args);
worksheet := ole2.GET_OBJ_PROPERTY(application,'ActiveSheet');
ole2.destroy_arglist(args);

Exception
when others then
Message('Error in opening Excel file ..!');
End;



---Creating Formula Cell
args := OLE2.CREATE_ARGLIST;
ole2.add_arg(args,2);
ole2.add_arg(args,1);
FormulaCell := ole2.GET_OBJ_PROPERTY(worksheet,'Cells',args);
ole2.destroy_arglist(args);

--- Creating Blank Formula Cell
args := OLE2.CREATE_ARGLIST;
ole2.add_arg(args,2);
ole2.add_arg(args,2);
BlankCell := ole2.GET_OBJ_PROPERTY(worksheet,'Cells',args);
ole2.destroy_arglist(args);

args := OLE2.CREATE_ARGLIST;
ole2.add_arg(args,1);
ole2.add_arg(args,1);
cell := ole2.GET_OBJ_PROPERTY(worksheet,'Cells',args);
ole2.destroy_arglist(args);

vValue := ole2.get_Char_property(cell,'Value');
msgbox.message(vvalue);

vCellAddress := ole2.get_Char_property(cell,'Address');

OLE2.Set_Property(FormulaCell, 'Formula', '=IsText('||vCellAddress||')');
OLE2.Set_Property(BlankCell, 'Formula', '=IsBlank('||vCellAddress||')');

OLE2.INVOKE(Workbook,'Save');

IsBlank := ole2.get_Char_property(BlankCell,'Text');
IsText := ole2.get_Char_property(FormulaCell,'Text');

If IsBlank='FALSE' and IsText='TRUE' Then
vValue := ole2.get_Char_property(cell,'Value');
Msgbox.Message('Value : '||vValue);
Else
Msgbox.Message('# Value Error #');
End If;





OLE2.RELEASE_OBJ(Formulacell);
OLE2.RELEASE_OBJ(Blankcell);
OLE2.RELEASE_OBJ(cell);
OLE2.RELEASE_OBJ(worksheet);
OLE2.RELEASE_OBJ(workbook);
OLE2.RELEASE_OBJ(workbooks);
OLE2.INVOKE(application,'Quit');
OLE2.RELEASE_OBJ(application);
Exception
when others then
message(sqlerrm);

OLE2.RELEASE_OBJ(Formulacell);
OLE2.RELEASE_OBJ(Blankcell);
OLE2.RELEASE_OBJ(cell);
OLE2.RELEASE_OBJ(worksheet);
OLE2.RELEASE_OBJ(workbook);
OLE2.RELEASE_OBJ(workbooks);
OLE2.INVOKE(application,'Quit');
OLE2.RELEASE_OBJ(application);
END;
----------------------------------------------------------


Thanks..
Re: Problem with working in OLE2 for EXCEL [message #394167 is a reply to message #393947] Wed, 25 March 2009 22:31 Go to previous messageGo to next message
mm_kanish05
Messages: 487
Registered: January 2007
Location: Chennai
Senior Member

Please check this code it should help ful to you

PACKAGE BODY Export2Excel IS
  Procedure OpenApplication(Filename varchar default 'New') Is
  Begin
  	  Application := ole2.create_obj('Excel.Application');
  	  Workbooks := ole2.get_obj_property(Application,'Workbooks');
  	  
  	  --Open Excisiting Excel File
  	  If Filename <> 'New' Then
  	     Arg := ole2.create_arglist;
  	     ole2.add_arg(Arg,Filename);
  	     Workbook := ole2.get_obj_property(Workbooks,'Open',Arg);
  	     ole2.destroy_arglist(arg);
  	  Else
  	  	 Workbook := ole2.invoke_obj(workbooks,'Add');
  	  End If;
  End OpenApplication;
  	  
  Procedure OpenWorksheet(Worksheetname varchar default 'New') is
  Begin 	  
  	  --Open Excisiting Excel Sheet
  	  If Worksheetname <> 'New' Then  	  	 
  	     Arg := ole2.create_arglist;
  	     ole2.add_arg(Arg,Worksheetname);
  	     WorkSheet := ole2.get_obj_property(Workbook,'WorkSheets',Arg);
  	     ole2.destroy_arglist(arg);
  	  Else
  	  	 WorkSheets := ole2.get_obj_property(Workbook,'WorkSheets');
  	  	 Worksheet := ole2.invoke_obj(Worksheets,'Add');
  	  End If;
  End OpenWorksheet;
  	    	  
  
  Procedure WriteIntoCell(rowno number,colno number,Fvalue varchar,typ varchar,border boolean, Font Boolean) Is
  Begin
  	  Arg := ole2.create_arglist;
  	  ole2.add_arg(Arg,rowno);
  	  ole2.add_arg(Arg,colno);
  	  Cell := ole2.get_obj_property(WorkSheet,'Cells',Arg);
  	  ole2.destroy_arglist(arg);
  	  
  	  If typ = 'CHAR' Then
  	  	 If Substr(Fvalue,1,1) = '0' Then  	  	 
            ole2.Set_property(Cell,'Value',''''||Fvalue);
  	  	 Else
  	  	 	  ole2.Set_property(Cell,'Value',Fvalue);
  	  	 End if;
         ole2.Set_property(Cell,'NumberFormat','@');
  	  Else
  	  	 If typ = 'NUMBER' Then
  	  	    ole2.Set_property(Cell,'Value',Fvalue);
  	  	    ole2.Set_property(Cell,'NumberFormat','####0.00');
  	  	 Else
  	  	 	  ole2.Set_property(Cell,'Value',Fvalue);
  	  	 End if;
  	  End if;  
  	  
  	  If Font Then
  	  	 ExcelFont := ole2.get_obj_property(Cell,'Font');
  	  	 ole2.Set_property(ExcelFont,'Bold','True');
  	  	 ole2.Set_property(ExcelFont,'Size',10);
  	  	 ole2.release_obj(ExcelFont);
  	  End If;	
      
  	  If Border Then
  	    V_border := ole2.get_obj_property(Cell,'Borders');
  	    ole2.Set_property(V_border,'LineStyle',1);
  	    ole2.release_obj(V_border);
  	  End If;	   	  

  	  ole2.release_obj(cell);  	  
  End WriteIntoCell;
  
  Function ReadFromCell Return Varchar is
  Begin
  	   Return(ole2.get_obj_property(Cell,'Value'));
  End ReadFromCell;
  	   
  
  Procedure VisibleApps(Val Boolean) Is
  Begin
  	ole2.Set_property(Application,'Visible',val);
  End;
  
  Procedure SaveFile(Filename varchar) Is
  Begin
  	  Arg := ole2.create_arglist;
  	  ole2.add_arg(Arg,Filename);
  	  ole2.set_property(workbook,'Save',Arg);
  	  ole2.destroy_arglist(arg);
  End SaveFile;
  
  Procedure ReleaseWorksheet(Filename varchar default 'New') Is
  Begin
  	 ole2.release_obj(Worksheet);
  	 If Filename = 'New' Then
  	    ole2.release_obj(WorkSheets);
  	 End If;
  end;
  
  Procedure CloseApplication Is
  Begin  	
  	ole2.release_obj(Workbook);
  	ole2.release_obj(Workbooks);
  	ole2.invoke(Application,'Quit');
  	ole2.release_obj(Application);
  End CloseApplication;
END;


And Check the ReleaseWorksheet and CloseApplication.

Kanish
Re: Problem with working in OLE2 for EXCEL [message #394987 is a reply to message #393947] Mon, 30 March 2009 23:52 Go to previous message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Have you solved your problem?

David
Previous Topic: Forms 6i Supports Multilingual - Simplified Chinese
Next Topic: Error ?3433?
Goto Forum:
  


Current Time: Tue Dec 06 02:26:15 CST 2016

Total time taken to generate the page: 0.12998 seconds