topic Re: Excel formatting using OLE in Application Development and Automation Discussions

Excel formatting using OLE

Former Member — Fri, 19 Aug 2016 10:20:59 GMT

I have come across a requirement where unused rows and columns need to be removed from excel sheet.

After the formatting, the excel sheet should look like below provided image .

Is it possible by using through OLE ?

Please let me know if further details required.

Regards,

Prabin

Re: Excel formatting using OLE

RaymondGiuseppi — Fri, 19 Aug 2016 10:29:35 GMT

Try to record this in Excel (record Macro) then analyze the generated macro to build your Abap code.

Re: Excel formatting using OLE

Former Member — Fri, 19 Aug 2016 11:02:17 GMT

Hi Raymond ,

I am new to Record Macro process .

Could you please give me few useful links or steps to record & generate macro in ABAP Program.

Thanks

Re: Excel formatting using OLE

Former Member — Fri, 19 Aug 2016 11:38:47 GMT

I already have generated the excel sheet by using OLE through ABAP program.

Now I Need to remove unused rows and columns . Which will make excel look like below provided image

Can i achieve the above formatting option using OLE ?

Is Record macro is the only option for this ?

Any input please.

Re: Excel formatting using OLE

RaymondGiuseppi — Fri, 19 Aug 2016 11:49:17 GMT

Recording a macro is only a tool to get the list of methods to use, also this is not an Office forum

Re: Excel formatting using OLE

Former Member — Fri, 19 Aug 2016 12:04:05 GMT

Since i was not aware Recording macro process so i thought probably by ABAP coding my requirement cab be achived.

Re: Excel formatting using OLE

RaymondGiuseppi — Fri, 19 Aug 2016 12:27:51 GMT

Solution will be some Abap code, but first you have to identify the method to call. Recording the macro will provide those to you. Then you "onmy" have to translate VBA syntax to Abap syntax.

Re: Excel formatting using OLE

Stefan-Schnell — Fri, 19 Aug 2016 12:56:38 GMT

Hello Prabin,

you can try this to delete empty cells in an Excel sheet via ABAP:

"-Begin-----------------------------------------------------------------

REPORT z_excel.

TYPE-POOLS ole2.

"-Constants-------------------------------------------------------------

CONSTANTS:

xlcelltypeblanks VALUE 4.

"-Variables-------------------------------------------------------------

DATA:

lo_application TYPE ole2_object,

lo_workbooks TYPE ole2_object,

lo_workbook TYPE ole2_object,

lo_worksheet TYPE ole2_object,

lo_cells TYPE ole2_object,

lo_cell TYPE ole2_object,

lo_selection TYPE ole2_object,

lo_range TYPE ole2_object.

"-Preparation of Excel--------------------------------------------------

CREATE OBJECT lo_application 'Excel.Application'.

SET PROPERTY OF lo_application 'Visible' = 1.

CALL METHOD OF lo_application 'Workbooks' = lo_workbooks.

CALL METHOD OF lo_workbooks 'Add' = lo_workbook.

GET PROPERTY OF lo_application 'ActiveSheet' = lo_worksheet.

"-Fill a few cells with empy cells between------------------------------

GET PROPERTY OF lo_application 'Range' = lo_range EXPORTING #1 = 'A1'.

CALL METHOD OF lo_range 'Select'.

GET PROPERTY OF lo_application 'ActiveCell' = lo_range.

SET PROPERTY OF lo_range 'FormulaR1C1' = '1'.

GET PROPERTY OF lo_application 'Range' = lo_range EXPORTING #1 = 'A3'.

CALL METHOD OF lo_range 'Select'.

GET PROPERTY OF lo_application 'ActiveCell' = lo_range.

SET PROPERTY OF lo_range 'FormulaR1C1' = '2'.

GET PROPERTY OF lo_application 'Range' = lo_range EXPORTING #1 = 'A5'.

CALL METHOD OF lo_range 'Select'.

GET PROPERTY OF lo_application 'ActiveCell' = lo_range.

SET PROPERTY OF lo_range 'FormulaR1C1' = '3'.

"=Code to delete empty cells begins here================================

"-Select all cells------------------------------------------------------

GET PROPERTY OF lo_worksheet 'Cells' = lo_cells.

CALL METHOD OF lo_cells 'Select'.

"-Select only blank cells-----------------------------------------------

GET PROPERTY OF lo_application 'Selection' = lo_selection.

CALL METHOD OF lo_selection 'SpecialCells' = lo_range

EXPORTING #1 = xlcelltypeblanks.

CALL METHOD OF lo_range 'Select'.

"-Delete blank cells----------------------------------------------------

CALL METHOD OF lo_range 'Delete'.

"-End-------------------------------------------------------------------

Let us know your results.

Cheers

Stefan

Re: Excel formatting using OLE

Former Member — Fri, 19 Aug 2016 13:19:48 GMT

Hi Stefan,

Definitely i will try the code snippet.

But before that just want to confirm once that my requirement is clear to you.

The requirement is to remove unused rows and columns from excel sheet.

Before formatting (Blank cell marked as red)

After formatting (No blank cell)

I think your code will remove the black records . (Which is not satisfying my requirement )

Please correct me if my assumption is wrong

Regards,

Prabin

Re: Excel formatting using OLE

Stefan-Schnell — Fri, 19 Aug 2016 13:42:31 GMT

Hello Prabin,

you are right, the code deletes every empty cell on the sheet, not only the columns and rows outside the range. But I think you can use xlCellTypeLastCell to get the last using cell and row and to set your ranges based on this information. This ranges you can delete on the same way.

Cheers

Stefan

Re: Excel formatting using OLE

Stefan-Schnell — Sat, 20 Aug 2016 08:11:18 GMT

Hello Prabin,

try this code:

"-Begin------------------------------------------------------------

REPORT z_excel.

TYPE-POOLS ole2.

"-Constants--------------------------------------------------------

CONSTANTS:

xlcelltypelastcell TYPE i VALUE 11,

false TYPE i VALUE 0.

"-Variables--------------------------------------------------------

DATA:

lo_application TYPE ole2_object,

lo_workbooks TYPE ole2_object,

lo_workbook TYPE ole2_object,

lo_worksheet TYPE ole2_object,

lo_cells TYPE ole2_object,

lo_range TYPE ole2_object,

lo_window TYPE ole2_object,

lv_lastcelladdr TYPE string,

lv_markcelladdr TYPE string,

lv_dum TYPE string,

lv_row TYPE n,

lv_col TYPE string,

lv_rng_beg TYPE string,

lv_rng_end TYPE string.

"-Preparation of Excel---------------------------------------------

CREATE OBJECT lo_application 'Excel.Application'.

SET PROPERTY OF lo_application 'Visible' = 1.

CALL METHOD OF lo_application 'Workbooks' = lo_workbooks.

CALL METHOD OF lo_workbooks 'Add' = lo_workbook.

GET PROPERTY OF lo_application 'ActiveSheet' = lo_worksheet.

"-Fill a few cells with empy cells between-------------------------

PERFORM SetCell USING lo_application 'A1' '1'.

PERFORM SetCell USING lo_application 'A3' '2'.

PERFORM SetCell USING lo_application 'A5' '3'.

PERFORM SetCell USING lo_application 'B2' '4'.

PERFORM SetCell USING lo_application 'B4' '5'.

PERFORM SetCell USING lo_application 'C3' '6'.

"-Get last cell which is in use------------------------------------

GET PROPERTY OF lo_application 'Range' = lo_range

EXPORTING #1 = 'A1'.

CALL METHOD OF lo_range 'SpecialCells' = lo_cells

EXPORTING #1 = xlcelltypelastcell.

GET PROPERTY OF lo_cells 'Address' = lv_lastcelladdr.

"-Get next cell which marks the range------------------------------

GET PROPERTY OF lo_application 'Range' = lo_range

EXPORTING #1 = lv_lastcelladdr.

CALL METHOD OF lo_range 'Select'.

GET PROPERTY OF lo_application 'ActiveCell' = lo_range.

GET PROPERTY OF lo_range 'Offset' = lo_range

EXPORTING #1 = 1 #2 = 1.

CALL METHOD OF lo_range 'Select'.

GET PROPERTY OF lo_range 'Address' = lv_markcelladdr.

SPLIT lv_markcelladdr AT '$' INTO lv_dum lv_col lv_row.

"-Select first range-----------------------------------------------

lv_rng_beg = lv_col && '1'.

lv_rng_end = 'XFD' && lv_row.

GET PROPERTY OF lo_application 'Range' = lo_range

EXPORTING #1 = lv_rng_beg #2 = lv_rng_end.

CALL METHOD OF lo_range 'Select'.

"-Delete cells-----------------------------------------------------

CALL METHOD OF lo_range 'Delete'.

"-Select second range----------------------------------------------

lv_rng_beg = 'A' && lv_row.

lv_rng_end = 'XFD1048576'.

GET PROPERTY OF lo_application 'Range' = lo_range

EXPORTING #1 = lv_rng_beg #2 = lv_rng_end.

CALL METHOD OF lo_range 'Select'.

"-Delete cells-----------------------------------------------------

CALL METHOD OF lo_range 'Delete'.

"-Disable grid view------------------------------------------------

GET PROPERTY OF lo_application 'ActiveWindow' = lo_window.

SET PROPERTY OF lo_window 'DisplayGridlines' = false.

"-Subroutines------------------------------------------------------

FORM SetCell USING po_application TYPE ole2_object

pv_cell TYPE string pv_value TYPE string.

"-Variables------------------------------------------------------

DATA:

lo_range TYPE ole2_object.

GET PROPERTY OF lo_application 'Range' = lo_range

EXPORTING #1 = pv_cell.

CALL METHOD OF lo_range 'Select'.

GET PROPERTY OF lo_application 'ActiveCell' = lo_range.

SET PROPERTY OF lo_range 'FormulaR1C1' = pv_value.

ENDFORM.

"-End--------------------------------------------------------------

This code detects the right bottom point of your range and deletes the ranges on the right and down side of your range, Last but not least it disables the grid lines.

Let us know your results.

Cheers

Stefan

Re: Excel formatting using OLE

Former Member — Fri, 26 Aug 2016 09:05:54 GMT

I worked on your sample program to achieve my requirement and got the required output.

Thank you .