OLE automation : Problems with using the 'END' property of range

[Former Member]

I want to append data to an existing excel file at the end. I want to use the 'END' property to get to the next empty row but I cannot get the 'END' property of class RANGE to work.

Here is the code that I use:

CREATE OBJECT excel_object 'EXCEL.APPLICATION' .

SET PROPERTY OF excel_object 'VISIBLE' = 1 .

GET PROPERTY OF excel_object 'WORKBOOKS' = workbook_list.

• Open file

CALL METHOD OF workbook_list 'OPEN' = workbook

EXPORTING #1 = filepath.

CALL METHOD OF workbook 'ACTIVATE'.

• Open first worksheet

CALL METHOD OF excel_object 'WORKSHEETS' = worksheet

EXPORTING #1 = 'STATS'.

CALL METHOD OF worksheet 'ACTIVATE'.

• Get last active cell

CALL METHOD of excel_object 'RANGE' = range

exporting #1 = 'A1'.

CALL METHOD of range 'SELECT'.

GET property of excel_object 'SELECTION' = range2.

CALL METHOD OF range2 'END' = range3 " <= fails at this

EXPORTING #1 = 'xlDown'.

I have prior experience with automation and have tried various combinations by using diffrent handles and code combination like trying to use GET PROPERTY instead of CALL METHOD or changing the order etc. This one best emulates the macro that was recorded in excel.

How do I get this to work....any ideas??

[Former Member]

Thanks guys....I solved this.....Though I cudnt get the end property to work but I used a work around....its logic and if you cant do it one way.....work around it

[Former Member]

Hi Sameer -

What was your solution to this problem? I'll be impressed if you respond right away since your last post was 3 years ago 🐵

Thanks,

Tim

[Former Member]

I was also searching for the answer to this for a while and would have appreciated a solution, so here it is!

I tried lots of variations on the End(xlDown) property but couldn't get it to work.

My workaround was to create a VBA procedure within Excel, and call that from OLE.

Create a new module in the Excel workbook.

Insert the following:

Option Explicit

Sub EndOfList(intColumn As Integer)

' Starting at the bottom, work upwards to find a populated cell
' in the specified column.  Then select the cell below it.
  Cells(65535, intColumn).End(xlUp).Offset(1, 0).Select

End Sub

Note that I used xlUp instead, as this allows empty rows to be used before the data rows that we want to append to.

The ABAP code:

REPORT zole.

TYPE-POOLS: ole2.

DATA: lv_excel   TYPE ole2_object,
      lv_books   TYPE ole2_object,
      lv_book    TYPE ole2_object,
      lv_sheet   TYPE ole2_object,
      lv_active  TYPE ole2_object,
      lv_cells   TYPE ole2_object,
      lv_row     TYPE i.

CONSTANTS: lc_book  TYPE string VALUE 'c:\test.xls',
           lc_macro TYPE string VALUE 'EndOfList'.

* Create Excel application and a Books object
  CREATE OBJECT lv_excel 'Excel.Application'.
  GET PROPERTY OF lv_excel 'Workbooks' = lv_books.

* Open book
  CALL METHOD OF lv_books 'Open' = lv_book
    EXPORTING
      #1 = lc_book.

* Activate the first Worksheet
  CALL METHOD OF lv_excel 'Worksheets' = lv_sheet
    EXPORTING
      #1 = 1.

  CALL METHOD OF lv_sheet 'Activate'.

* Run VBA to position on the next free row
  CALL METHOD OF lv_excel 'Run'
    EXPORTING
      #1 = lc_macro
      #2 = 1.

* Get the row of the active cell
  GET PROPERTY OF lv_excel 'ActiveCell' = lv_active.
  GET PROPERTY OF lv_active 'Row' = lv_row.
  FREE OBJECT lv_active.

* Get the first column of the selected row
  CALL METHOD OF lv_sheet 'Cells' = lv_cells
    EXPORTING
      #1 = lv_row
      #2 = 1.

* Populate the cell
  SET PROPERTY OF lv_cells 'Value' = 'XXXXX'.
  FREE OBJECT lv_cells.

* Save, close the workbook and exit Excel
  CALL METHOD OF lv_book 'Save'.
  CALL METHOD OF lv_books 'Close'.
  CALL METHOD OF lv_excel 'Quit'.

* Free all used objects (see OSS note #129994)
  FREE OBJECT lv_sheet.
  FREE OBJECT lv_book.
  FREE OBJECT lv_books.
  FREE OBJECT lv_excel.

Note that you should check SY-SUBRC after each OLE call (I've removed my checks for simplicity). Also, refer to OSS note #129994 regarding the need to free OLE objects after use.

Hope this helps someone!

Pete