OraFAQ Forum: Forms » OLE2 for formating Excel Sheet

Home » Developer & Programmer » Forms » OLE2 for formating Excel Sheet (Forms6i, Excel 2003)

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

OLE2 for formating Excel Sheet [message #345328]

Wed, 03 September 2008 01:45

prachi.mgorwadkar
Messages: 75
Registered: May 2007
Location: Pune

Member

Hello All,

I have one form through I have created one Excel Sheet.

I want to format this sheet through my form, where I want to AutoFit particular columns i.e. "B:L".

But, form does not accept double quotes.

I have written a procedure as below:

PROCEDURE auto_fit IS
BEGIN
args:=OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, 'D:E');
range := OLE2.GET_OBJ_PROPERTY( worksheet,'UsedRange', args);
range_col := OLE2.GET_OBJ_PROPERTY( range,'Columns');
OLE2.INVOKE( range_col,'AutoFit' );
OLE2.DESTROY_ARGLIST(args);
OLE2.RELEASE_OBJ( range );
OLE2.RELEASE_OBJ( range_col );
END;

And called this Procedure at the end, means after inserting all data in the excel file.

But, this Procedure does not work. It gives non-ORACLE error.

Can anybody give me any solution?

Report message to a moderator

Re: OLE2 for formating Excel Sheet [message #345329 is a reply to message #345328]

Wed, 03 September 2008 02:01

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

Have you looked at all the other 'ole excel' entries in this forum?

I suggest using PM to contact some of the posters in those threads.

David

Report message to a moderator

Re: OLE2 for formating Excel Sheet [message #345339 is a reply to message #345328]

Wed, 03 September 2008 02:41

prachi.mgorwadkar
Messages: 75
Registered: May 2007
Location: Pune

Member

Sir,

Thanks for the reply.

I have tried searching it yesterday, but did not get the solution I wanted. So, today I posted it.

Though, I will try searching.

Thanks,

- Prachi

Report message to a moderator

Re: OLE2 for formating Excel Sheet [message #345343 is a reply to message #345339]

Wed, 03 September 2008 02:46

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

Have you tried '"a;b"'? That is, put the double quotes inside single quotes? Have you tried multiple double quotes and multiple single quotes as Forms and Excel may be stripping the 'outer' ones?

I don't think your question has been asked before in this forum. It is a Microsoft question anyway.

PM the other people and hope they have some ideas.

David

Report message to a moderator

Re: OLE2 for formating Excel Sheet [message #345346 is a reply to message #345328]

Wed, 03 September 2008 02:50

prachi.mgorwadkar
Messages: 75
Registered: May 2007
Location: Pune

Member

Sir,

I have tried:

"B:E"

'"B:E"'

'B:E'

Nothing works.

One Procedure I found as :

PROCEDURE auto_fit(p_col IN NUMBER) IS

v_args ole2.list_type;

v_col ole2.obj_type;

BEGIN

v_args :=ole2.create_arglist;
ole2.add_arg(v_args, p_col);
v_col := ole2.get_obj_property(v_worksheet, 'Columns', v_args);
ole2.invoke(v_col, 'AutoFit');
ole2.destroy_arglist(v_args);
ole2.release_obj(v_col);
END auto_fit;

But this is only for single column, not for a range.

Thanks,
- Prachi

Report message to a moderator

Re: OLE2 for formating Excel Sheet [message #345493 is a reply to message #345346]

Wed, 03 September 2008 19:13

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

You say "Nothing works" but you have indicated that you have only tried three options. I suggested multiple pair of quotes. Did you try them? What was their result?

Back to your original post: what was the non-Oracle error that you recevied?

Have you considered using a loop to issue a format command per column?

Can you do this operation using a Java or MS product?

David

Report message to a moderator

Re: OLE2 for formating Excel Sheet [message #345537 is a reply to message #345493]

Thu, 04 September 2008 01:10

mudabbir
Messages: 235
Registered: April 2006
Location: Kuwait

Senior Member

I am using this code and it works fine in 6i and also in 10g(WITH CLIENT_OLE2)

ExcelArgs := ole2.create_arglist;
OLE2.ADD_ARG(ExcelArgs, 'C:I'); 
column3 := OLE2.GET_OBJ_PROPERTY(ExcelWorkSheetID, 'Columns', ExcelArgs);
OLE2.DESTROY_ARGLIST(ExcelArgs);
OLE2.SET_PROPERTY(column3, 'ColumnWidth', 12);
OLE2.RELEASE_OBJ(column3);

Report message to a moderator

Re: OLE2 for formating Excel Sheet [message #346152 is a reply to message #345493]

Sat, 06 September 2008 10:30

prachi.mgorwadkar
Messages: 75
Registered: May 2007
Location: Pune

Member

Thanks for the replies Sir.

But, the problem is now solved.

-Prachi

Report message to a moderator

Previous Topic:	POPUP Confirmation in oracle Form
Next Topic:	Menu Proble.....

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Sep 11 00:48:02 CDT 2025