How to sort data in excel sheet using ole automation method

[Former Member]

Hi

i have generated a report in excel format using ole automation methods. The generated excel contains two work sheets. I need to sort data in the second sheet by 2 specified columns using excel macro . The data starts from row 13 onwards where as i have 12th row as column headers. The data needs to be sorted before the sheet display.

Can any body advice me how to achive this.

[Former Member]

Hi,

You can sort the internal table in your program, and then download it.

Regards,

Nisha Vengal.

[Former Member]

Hi Nisha,

My question is howto sort the data using OLE methods with excel macro but not by sorting the internal table. Even though we can achieve this with sorting internal table , i need to use OLE methods for sorting.

Thank you

[former_member189059]

Hi,

Try using this code

*&---------------------------------------------------------------------*
*& Report  ZKRIS_OLE_SORT
*&
*&---------------------------------------------------------------------*
*& Author  : Kris Donald
*& Date    : 27-11-2009
*& Purpose : Write some data to Excel and sort it
*&---------------------------------------------------------------------*
*& Date Changed by Tag Description
*&
*&---------------------------------------------------------------------*

REPORT  ZKRIS_OLE_SORT.

TYPE-POOLS OLE2 .
DATA:  COUNT TYPE I,
       APPLICATION TYPE OLE2_OBJECT,
       WORKBOOK TYPE OLE2_OBJECT,
       EXCEL     TYPE OLE2_OBJECT,
       H_SHEET TYPE OLE2_OBJECT.
CONSTANTS: ROW_MAX TYPE I VALUE 16384. "256.
DATA INDEX TYPE I.

DATA: H_CELL TYPE OLE2_OBJECT.


CREATE OBJECT EXCEL 'EXCEL.APPLICATION'.

IF SY-SUBRC NE 0.
  WRITE: / 'No EXCEL creation possible'.
  STOP.
ENDIF.

SET PROPERTY OF EXCEL 'DisplayAlerts' = 0.
CALL METHOD OF EXCEL 'WORKBOOKS' = WORKBOOK .
SET PROPERTY OF EXCEL 'VISIBLE' = 1.

* creating workbook
SET PROPERTY OF EXCEL 'SheetsInNewWorkbook' = 1.
CALL METHOD OF WORKBOOK 'ADD'.

CALL METHOD OF EXCEL 'WORKSHEETS' = H_SHEET
  EXPORTING
    #1 = 1.

CALL METHOD OF H_SHEET 'ACTIVATE'.

* write some data
PERFORM populate_cells.

DATA: h_sort              TYPE OLE2_OBJECT.
DATA: h_sortfields        TYPE OLE2_OBJECT.

DATA: h_range_entire      TYPE OLE2_OBJECT.
DATA: h_range_sortfield1  TYPE OLE2_OBJECT.
DATA: h_range_sortfield2  TYPE OLE2_OBJECT.

GET PROPERTY OF H_SHEET 'Sort' = h_sort.
GET PROPERTY OF H_Sort 'SortFields' = h_sortfields.

* clear current sort fields
CALL METHOD OF h_sortfields 'Clear'.

* define ranges

* data range
CALL METHOD OF EXCEL 'Range' = h_range_entire
  EXPORTING
    #1 = 'C13'
    #2 = 'D22'.

* search range 1
CALL METHOD OF EXCEL 'Range' = h_range_sortfield1
  EXPORTING
    #1 = 'C13'
    #2 = 'C21'.

* search range 2
CALL METHOD OF EXCEL 'Range' = h_range_sortfield2
  EXPORTING
    #1 = 'D13'
    #2 = 'D21'.

[former_member189059]

* add the sort criteria
CALL METHOD OF h_sortfields 'Add'
  EXPORTING
    #1 = h_range_sortfield1
    #2 = 0
    #3 = 1
    #4 = 0.

CALL METHOD OF h_sortfields 'Add'
  EXPORTING
    #1 = h_range_sortfield2
    #2 = 0
    #3 = 1
    #4 = 0.


* set the range
CALL METHOD OF h_sort 'SetRange'
  EXPORTING
    #1 = h_range_entire.

* set properties
set PROPERTY OF h_sort 'Header' = 1.
set PROPERTY OF h_sort 'MatchCase' = 0.
set PROPERTY OF h_sort 'Orientation' = 1.
set PROPERTY OF h_sort 'SortMethod ' = 1.

CALL METHOD OF h_sort 'Apply'.

[former_member189059]

*&---------------------------------------------------------------------*
*&      Form  POPULATE_CELLS
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
form POPULATE_CELLS .

DATA: lv_row type i.

* header
lv_row = 12.
PERFORM write_cell using lv_row 3 'Val'.
PERFORM write_cell using lv_row 4 'Attr'.

* data
lv_row = lv_row + 1.
PERFORM write_cell using lv_row 3 'a'.
PERFORM write_cell using lv_row 4 '1'.

lv_row = lv_row + 1.
PERFORM write_cell using lv_row 3 'a'.
PERFORM write_cell using lv_row 4 '6'.

lv_row = lv_row + 1.
PERFORM write_cell using lv_row 3 'b'.
PERFORM write_cell using lv_row 4 '4'.

lv_row = lv_row + 1.
PERFORM write_cell using lv_row 3 'a'.
PERFORM write_cell using lv_row 4 '2'.

lv_row = lv_row + 1.
PERFORM write_cell using lv_row 3 'b'.
PERFORM write_cell using lv_row 4 '2'.

lv_row = lv_row + 1.
PERFORM write_cell using lv_row 3 'a'.
PERFORM write_cell using lv_row 4 '3'.

lv_row = lv_row + 1.
PERFORM write_cell using lv_row 3 'b'.
PERFORM write_cell using lv_row 4 '0'.


endform.                    " POPULATE_CELLS


*&---------------------------------------------------------------------*
*&      Form  WRITE_CELL
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      -->P_LV_ROW  text
*      -->P_3      text
*      -->P_0329   text
*----------------------------------------------------------------------*
form WRITE_CELL  using    lv_row
                          lv_col
                          lv_value.

CALL METHOD OF H_SHEET 'Cells' = H_CELL
  EXPORTING
    #1 = lv_row
    #2 = lv_col.
SET PROPERTY OF H_CELL 'Value' = lv_value.

endform.                    " WRITE_CELL