Excel formatting using OLE

[Former Member]

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

[RaymondGiuseppi]

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

[Former Member]

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

[Former Member]

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.

[RaymondGiuseppi]

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

[Former Member]

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

[RaymondGiuseppi]

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.

[Stefan-Schnell]

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

[Former Member]

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

[Stefan-Schnell]

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

[Stefan-Schnell]

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

[Former Member]

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

Thank you .