Skip to Content
author's profile photo Former Member
Former Member

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

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??

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • author's profile photo Former Member
    Former Member
    Posted on May 30, 2007 at 03:21 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Sep 13, 2010 at 08:37 PM

    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 :o)

    Thanks,

    Tim

    Add a comment
    10|10000 characters needed characters exceeded

    • 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

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.