Merge Excel Column via OLE2

[ekobudy]

I want to merge excel column from A:G, how can I do it ?

Thanks.

 

[bollineni]

this example might help... i think there might be a function for merge... basically give the columns u have to merge as a range object and then apply the merge method...
The below example is not exactly the one which willhelp.. but it might give u an idea.....

DECLARE
application OLE2.OBJ_TYPE;
workbook OLE2.OBJ_TYPE;
workbooks OLE2.OBJ_TYPE;
worksheet OLE2.OBJ_TYPE;
worksheets OLE2.OBJ_TYPE;
cell OLE2.OBJ_TYPE;
column OLE2.OBJ_TYPE;
row OLE2.OBJ_TYPE;
font 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');
worksheet := OLE2.INVOKE_OBJ(worksheets,'Add');

--Set default for columns F, G and H
args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, 'F:H'); --Columns F thru H
column := OLE2.GET_OBJ_PROPERTY(worksheet, 'Columns', args);
OLE2.DESTROY_ARGLIST(args);
font := OLE2.GET_OBJ_PROPERTY(column, 'Font', args);
OLE2.SET_PROPERTY(font, 'Name', 'Courier New');
OLE2.SET_PROPERTY(font, 'Size', 12);
OLE2.SET_PROPERTY(font, 'Strikethrough', 'True');
OLE2.SET_PROPERTY(font, 'Bold', 'True');
OLE2.SET_PROPERTY(font, 'ColorIndex', 5); --Dark Blue
OLE2.RELEASE_OBJ(font);
--Set width for columns F thru H to 25
OLE2.SET_PROPERTY(column, 'ColumnWidth', 25);
OLE2.RELEASE_OBJ(column);

******************NUMBER FORMAT PROPERTY IS VERY IMPORTANT...........*******

--Set default for rows 1 through 20
args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, '1:20'); --Rows 1 thru 20
row := OLE2.GET_OBJ_PROPERTY(worksheet, 'Rows', args);
OLE2.DESTROY_ARGLIST(args);
OLE2.SET_PROPERTY(row , 'RowHeight', 12);
OLE2.RELEASE_OBJ(row);

--Set row 4 height
args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, 4); --Row 4
row := OLE2.GET_OBJ_PROPERTY(worksheet, 'Rows', args);
OLE2.DESTROY_ARGLIST(args);
OLE2.SET_PROPERTY(row , 'RowHeight', 25);
OLE2.RELEASE_OBJ(row);

--Set cell B4
args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, 4); --Row 4
OLE2.ADD_ARG(args, 2); --Column B
cell := OLE2.GET_OBJ_PROPERTY(worksheet, 'Cells', args);
OLE2.DESTROY_ARGLIST(args);
OLE2.SET_PROPERTY(cell, 'Value', 'Hello Excel!');
font := OLE2.GET_OBJ_PROPERTY(cell, 'Font', args);
OLE2.SET_PROPERTY(font, 'Name', 'Arial');
OLE2.SET_PROPERTY(font, 'FontStyle', 'Italic');
OLE2.SET_PROPERTY(font, 'Size', 20);
-- possible Underline values = 2, 4, 5, -4119
OLE2.SET_PROPERTY(font, 'Underline', -4119);
OLE2.SET_PROPERTY(font, 'Bold', 'True');
OLE2.SET_PROPERTY(font, 'ColorIndex', 3); --Red
OLE2.RELEASE_OBJ(font);
OLE2.RELEASE_OBJ(cell);

--Set cell F4
args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, 4);
OLE2.ADD_ARG(args, 6);
cell := OLE2.GET_OBJ_PROPERTY(worksheet, 'Cells', args);
OLE2.DESTROY_ARGLIST(args);
OLE2.SET_PROPERTY(cell, 'Value', 'Good Bye Excel!');
OLE2.RELEASE_OBJ(cell);

--Set cell B7
args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, 7);
OLE2.ADD_ARG(args, 3);
cell := OLE2.GET_OBJ_PROPERTY(worksheet, 'Cells', args);
OLE2.DESTROY_ARGLIST(args);
OLE2.SET_PROPERTY(cell, 'Value', 9);
OLE2.RELEASE_OBJ(cell);

--Set cell C7
args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, 7);
OLE2.ADD_ARG(args, 'B');
cell := OLE2.GET_OBJ_PROPERTY(worksheet, 'Cells', args);
OLE2.DESTROY_ARGLIST(args);
OLE2.SET_PROPERTY(cell, 'Value', 4);
OLE2.RELEASE_OBJ(cell);

--Set formula for cell D7
args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, '7');
OLE2.ADD_ARG(args, 'D');
cell := OLE2.GET_OBJ_PROPERTY(worksheet, 'Cells', args);
OLE2.DESTROY_ARGLIST(args);
OLE2.SET_PROPERTY(cell, 'Formula', '=B7*C7');
OLE2.RELEASE_OBJ(cell);

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