Deleting Worksheets/Resize Header Font in Excel, using OLE2 2

[krindler]

Hello...

I just have a few more questions - hopefully they are the last as I finish up this report - but you have been such a huge help!! Thank-You!!

1. How can I Delete Unused Sheets (i.e., Sheet 1, Sheet 2, & Sheet 3 )?

2. How do I rename a Sheet (i.e., From 'Sheet 4' to 'Payment Report' )?

3. Is there a way to resize the Header. I have the header to read what I want it to ('Payment Report'), but would like the font size to be 8, rather than 10.

Any help you could give me on the above would be appreciated!!

Thanks In Advance,
Kelly

 

[sfvb]

DECLARE
application OLE2.OBJ_TYPE;
workbooks OLE2.OBJ_TYPE;
workbook OLE2.OBJ_TYPE;
worksheets OLE2.OBJ_TYPE;
worksheet OLE2.OBJ_TYPE;
pagesetup OLE2.OBJ_TYPE;

args OLE2.List_Type;
BEGIN
application := OLE2.CREATE_OBJ('Excel.Application');
OLE2.SET_PROPERTY(application, 'Visible', TRUE);

workbooks := OLE2.GET_OBJ_PROPERTY(application, 'Workbooks');
workbook := OLE2.INVOKE_OBJ(workbooks, 'Add');
worksheets := OLE2.GET_OBJ_PROPERTY(workbook, 'Worksheets');

--- Delete all but one worksheet
OLE2.SET_PROPERTY(application, 'DisplayAlerts', FALSE);
WHILE OLE2.GET_NUM_PROPERTY(worksheets , 'Count') > 1
LOOP
--- Get a handle to the 2nd worksheet and delete it
args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, 2);
worksheet := OLE2.GET_OBJ_PROPERTY(workbook, 'Worksheets', args);
OLE2.DESTROY_ARGLIST(args);
OLE2.INVOKE(worksheet, 'Delete');
OLE2.RELEASE_OBJ(worksheet);
END LOOP;
OLE2.SET_PROPERTY(application, 'DisplayAlerts', TRUE);

--- Make the 1st Worksheet the active sheet
args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, 1);
worksheet := OLE2.GET_OBJ_PROPERTY(workbook, 'Worksheets', args);
OLE2.DESTROY_ARGLIST(args);
OLE2.INVOKE(worksheet, 'Select');

--- Rename the sheet
OLE2.SET_PROPERTY(worksheet, 'Name', 'My Only Sheet');

--- Change Font and FontSize for Header
pagesetup := OLE2.GET_OBJ_PROPERTY(worksheet, 'PageSetup');
OLE2.SET_PROPERTY(pagesetup, 'CenterHeader', '&"Tahoma,Bold" &14This is My Header');

OLE2.RELEASE_OBJ(pagesetup);
OLE2.RELEASE_OBJ(worksheet);
OLE2.RELEASE_OBJ(worksheets);
OLE2.RELEASE_OBJ(workbook);
OLE2.RELEASE_OBJ(workbooks);
OLE2.RELEASE_OBJ(application);
EXCEPTION
WHEN others THEN
message('Error');
END;



If you only wanted to change the fontsize on the header then:
--- Change just the FontSize for Header
pagesetup := OLE2.GET_OBJ_PROPERTY(worksheet, 'PageSetup');
OLE2.SET_PROPERTY(pagesetup, 'CenterHeader', '&14This is My Header');

 

[krindler]

I have one more quick question regarding resizing Headers. I am able to resize all of my headers & footers the way I need to, but I am unable to change the size when inserting a variable v_statement_date in the Left Footer.

When I use the '&8v_statement_date' it puts 'v_statement_date' in the field, using the correct fontsize, which I assumed it would do.

If anyone knows how I can put the correct size, using the v_statement_date variable, I would really appreciate any help you could offer me.

Thanks In Advance!

 

[sfvb]

Could you post the part of the code that is misbehaving?

 

[krindler]

Here is the code I am having trouble with...

hPageSetObj := OLE2.GET_OBJ_PROPER(worksheet, 'pagesetup' );
OLE2.SET_PROPERT
( hPageSetObj, 'LeftFooter', v_statement_date );
OLE2.RELEASE_OBJ ( hPageSetObj );

I would like the 'v_statement_date' to be displayed at a size of 8, rather than the default (which I believe is 10).

Please let me know if you have any questions or would like to see additional information.

Thanks!!

 

[sfvb]

You need to use the '&8' like you have been, but you need to concatenate the variable that you're using for the date. Ex:
OLE2.SET_PROPERTY(hPageSetObj, 'LeftFooter', '&8' || v_statement_date);

Note: When concatenating to the font size, if the first character following the font size is a number, then add a space between the fontsize and the number. Ex:

v_statement_date := 'Sep-13-2002';
OLE2.SET_PROPERTY(hPageSetObj, 'LeftFooter', '&8' || v_statement_date);

v_statement_date := '9-13-2002';
OLE2.SET_PROPERTY(hPageSetObj, 'LeftFooter', '&8 ' || v_statement_date);

 

[krindler]

Thank-You.

The solution worked great!