OLE2 objects: Select several ranges at once

[RicardoRomero_1]

Hello,

I'm experiencing some performance issues using OLE2 objects.

I'm aware of the ABAP2XLSX project — I use it in other developments and it works very well and is quite fast. However, in this case, I can't use it because we have an XLSM file stored on the server as a template. This file contains macros and validations written in Visual Basic. What we do is download this "template" and fill it with data using OLE2. As far as I know, ABAP2XLSX can't handle this scenario.

To improve performance in the program that uses OLE2, I avoid filling cells one by one. Instead, I use copy/paste from the clipboard and try to group operations by cell ranges to perform them all at once.

However, I'm still facing performance problems because some OLE2 method calls internally trigger the function AC_FLUSH_CALL_INTERNAL. In the latest ST12 trace, I see that this function is called around 2,600 times, consuming the vast majority of the program's runtime.

I'd like to be able to select multiple ranges at once to perform the same operation on several cells simultaneously, but this doesn't seem to work correctly. In Visual Basic, a simple instruction like:

Range("I11,N12,P8:P11,U12,U8").Select

works just fine, but when I try to "translate" it to OLE2, it doesn't seem to function.

For example, this works:

Range("P8:P11").Select

In other words, it seems that selecting multiple ranges at once is not supported (or maybe I'm doing it incorrectly).

I'll include a sample code that you can run to verify what I'm describing.

Do you know if it's possible to select multiple ranges at once using OLE2 objects?

REPORT zric_ole2.

TYPE-POOLS: soi,ole2.

PARAMETERS:
  pa_opt1 RADIOBUTTON GROUP rb1 DEFAULT 'X',
  pa_opt2  RADIOBUTTON GROUP rb1.

DATA:  lo_application   TYPE  ole2_object,
       lo_workbook      TYPE  ole2_object,
       lo_workbooks     TYPE  ole2_object,
       lo_range         TYPE  ole2_object,
       lo_worksheet     TYPE  ole2_object,
       lo_cell          TYPE  ole2_object,
       lo_cellstart      TYPE ole2_object,
       lo_cellend        TYPE ole2_object,
       lo_selection      TYPE ole2_object.

DATA: lv_selected_folder TYPE string,
      lv_complete_path   TYPE char256,
      lv_titulo          TYPE string.

CALL METHOD cl_gui_frontend_services=>directory_browse
  EXPORTING
    window_title    = lv_titulo
    initial_folder  = 'C:\'
  CHANGING
    selected_folder = lv_selected_folder
  EXCEPTIONS
    cntl_error      = 1
    error_no_gui    = 2
    OTHERS          = 3.
CHECK NOT lv_selected_folder IS INITIAL.

CREATE OBJECT lo_application 'Excel.Application'.
CALL METHOD OF lo_application 'Workbooks' = lo_workbooks.
CALL METHOD OF lo_workbooks 'Add' = lo_workbook.
SET PROPERTY OF lo_application 'Visible' = 0.
GET PROPERTY OF lo_application 'ACTIVESHEET' = lo_worksheet.


*--------------------------------------------------------------------*
* add some text to cell A1
CALL METHOD OF lo_worksheet 'Cells' = lo_cell
  EXPORTING
  #1 = 1
  #2 = 1.
CALL METHOD OF lo_cell 'SELECT'.
SET PROPERTY OF lo_cell 'Value' = 'TEST A1'.
*--------------------------------------------------------------------*
* add some text to cell A2
CALL METHOD OF lo_worksheet 'Cells' = lo_cell
  EXPORTING
  #1 = 1
  #2 = 2.
CALL METHOD OF lo_cell 'SELECT'.
SET PROPERTY OF lo_cell 'Value' = 'TEST A2'.
*--------------------------------------------------------------------*
* add some text to cell B3
CALL METHOD OF lo_worksheet 'Cells' = lo_cell
  EXPORTING
  #1 = 2
  #2 = 3.
CALL METHOD OF lo_cell 'SELECT'.
SET PROPERTY OF lo_cell 'Value' = 'TEST B3'.
*--------------------------------------------------------------------*

* Lock cells:

* First all un-locked
  CALL METHOD OF lo_application 'cells' = lo_cell.
  CALL METHOD OF lo_cell 'select'.
  CALL METHOD OF lo_application 'selection' = lo_selection.
  SET PROPERTY OF lo_selection 'locked' = 0.

*--------------------------------------------------------------------*
*--------------------------------------------------------------------*

* First option, its working, selecting ranges one by one, but bad performance in I need to do it may times
*--------------------------------------------------------------------*
  IF pa_opt1 EQ abap_true.

* Lock cells A1 and A2 at once
    CALL METHOD OF lo_worksheet 'cells' = lo_cellstart
      EXPORTING
      #1 = 1
      #2 = 1.
    CALL METHOD OF lo_worksheet 'cells' = lo_cellend
      EXPORTING
      #1 = 1
      #2 = 2.
    CALL METHOD OF lo_worksheet 'range' = lo_range
      EXPORTING
      #1 = lo_cellstart
      #2 = lo_cellend.

    CALL METHOD OF lo_range 'select'.
    CALL METHOD OF lo_application 'selection' = lo_selection.
    SET PROPERTY OF lo_selection 'locked' = 1.

* Lock cell B3
    CALL METHOD OF lo_worksheet 'range' = lo_range
      EXPORTING
      #1 = 'B3'.
    CALL METHOD OF lo_range 'select'.
    CALL METHOD OF lo_application 'selection' = lo_selection.
    SET PROPERTY OF lo_selection 'locked' = 1.

*--------------------------------------------------------------------*
*--------------------------------------------------------------------*
  ELSE.
*--------------------------------------------------------------------*

* Second option, trying to select several ranges at once, is not working...
*--------------------------------------------------------------------*

*   If I use a single cell or range its working:
*    CALL METHOD OF lo_worksheet 'range' = lo_range
*      EXPORTING
*      #1 = 'A1:B1'.
*    CALL METHOD OF lo_range 'select'.
*    CALL METHOD OF lo_application 'selection' = lo_selection.
*    SET PROPERTY OF lo_selection 'locked' = 1.

*   But if I use several ranges, then its not working:
    CALL METHOD OF lo_worksheet 'range' = lo_range
      EXPORTING
      #1 = 'A1,B1,C1'. "Or 'A1:B1, C2, D3:E4'
    CALL METHOD OF lo_range 'select'.
    CALL METHOD OF lo_application 'selection' = lo_selection.
    SET PROPERTY OF lo_selection 'locked' = 1.
  ENDIF.

*--------------------------------------------------------------------*
*--------------------------------------------------------------------*
*--------------------------------------------------------------------*

* Protect the excel sheet:
  CALL METHOD OF lo_worksheet 'protect'
    EXPORTING
      #01 = '1234'
      #02 = 0.

CONCATENATE lv_selected_folder '\Test' INTO lv_complete_path.

CALL METHOD OF lo_workbook 'SaveAs'
  EXPORTING
  #1 = lv_complete_path.
IF sy-subrc EQ 0.
  MESSAGE 'File downloaded successfully' TYPE 'S'.
ELSE.
  MESSAGE 'Error downloading the file' TYPE 'E'.
ENDIF.

CALL METHOD OF lo_application 'QUIT'.
FREE OBJECT lo_worksheet.
FREE OBJECT lo_workbook.
FREE OBJECT lo_application.

Thanks in advance.

PS: I'm using a table for the code in the post because the button "insert code sample" is not working for me.

[Sandra_Rossi]

If you know the VBA code for doing it, you can convert it to OLE manually (compatibility is around 99%). The Excel VBA Recorder shows you the VBA code for any user action. If I select the cells A1, C1 and E1, the obtained code is Range("A1,C1,E1").Select. Also: https://stackoverflow.com/questions/58294820/select-multiple-ranges-with-vba.

[RicardoRomero_1]

Hello, Sandra.

Yes, I know that it's usually possible to translate almost any code written in VBA to OLE, and that's exactly why I'm asking for your help—because this time I haven't been able to get it to work.

I've recorded macros in Excel to see the VBA code generated when selecting multiple ranges, and it's quite simple—the one I posted above.
But I haven't been able to make it work using OLE.

I created this blog many years ago to help others use OLE2 objects. I'm not an expert, but I've managed to work quite well with these objects for a long time
https://community.sap.com/t5/application-development-and-automation-blog-posts/using-ole2-objects-to...

[RicardoRomero_1]

.

[Sandra_Rossi]

Did you try what I proposed?

[RicardoRomero_1]

Yes, I had already tried what you mentioned—creating macros in Visual Basic and attempting to translate them to OLE—but it doesn’t work.

*   If I use a single cell or range its working:
*    CALL METHOD OF lo_worksheet 'range' = lo_range
*      EXPORTING
*      #1 = 'A1:B1'.
*    CALL METHOD OF lo_range 'select'.
*    CALL METHOD OF lo_application 'selection' = lo_selection.
*    SET PROPERTY OF lo_selection 'locked' = 1.

*   But if I use several ranges, then its not working:
    CALL METHOD OF lo_worksheet 'range' = lo_range
      EXPORTING
      #1 = 'A1,B1,C1'. "Or 'A1:B1, C2, D3:E4'
    CALL METHOD OF lo_range 'select'.
    CALL METHOD OF lo_application 'selection' = lo_selection.
    SET PROPERTY OF lo_selection 'locked' = 1.

I read the link you shared, but it doesn’t provide a solution either. I could use the UNION method to combine ranges, but that method only takes two arguments , and I would need to call it many times in my scenario. What I’m trying to avoid is making so many calls between SAP and Excel, to reduce the use of those FLUSH functions as much as possible. That’s why I wanted to pass a string with all the ranges separated by commas (,), which works fine in VBA, but doesn’t seem to work correctly in OLE.

EDIT: Union accept a lot of arguments:
expresión. Unión (Arg1, Arg2, Arg3, Arg4, Arg5, Arg6, Arg7, Arg8, Arg9, Arg10, Arg11, Arg12, Arg13, Arg14, Arg15, Arg16, Arg17, Arg18, Arg19, Arg20, Arg21, Arg22, Arg23, Arg24, Arg25, Arg26, Arg27, Arg28, Arg29, Arg30)
https://learn.microsoft.com/es-es/office/vba/api/excel.application.union

But I have huge excel files, dont know if this amount of argument is enough. I'll try.

[Sandra_Rossi]

Too bad, so you're in the 1% incompatible. abap2xlsx can read and write an XLSM file with VBA code, see ZCL_EXCEL methods which belong to the interface ZIF_EXCEL_BOOK_VBA_PROJECT. I never tried them. If your Excel file is complex to fill, you may also use the solution demonstrated by zdemo_excel_fill_template.

[RicardoRomero_1]

Well, as I mentioned earlier, the version of my project is very old, ABAP 701, and I copied the ABAP2XLSX solution a long time ago from other project.

I had to fix many code errors to make it work on my version, so I don't have the latest updates and I can't use the example ZDEMO_EXCEL_FILL_TEMPLATE.

In any case, the question is about an already completed development in my project, which is very large and uses OLE. The question is not about using other technologies or reengineering everything, which would take a lot of time. It's just a simple question about OLE2, to see if it's possible to select more than one range at the same time. In VBA it's very easy, but I can't manage to do it with OLE.

In the example I provided, you can quickly test it by copying and pasting: with option 1 (a single range), it works fine; with option 2 (multiple ranges), it doesn't work correctly.

[abo]

Have you indeed tried with abap2xlsx? AFAIK the template functionality should work.

[RicardoRomero_1]

hello, As far as I know (correct me if I'm wrong) abap2xls cannot use files with embedded macros. abap2xlsx does not support embedding or preserving VBA modules (macros), because VBA code is stored in a binary format, not in the Open XML structure.

[abo]

Hmm, haven't tried that yet but you could tweak the demo above, change the template to one with macros and see if smoke comes out. There is an interesting comment in the source: https://github.com/abap2xlsx/abap2xlsx/blob/08c794f3ca9d47d84073222c9fb39b2a9fbf3116/src/zcl_excel_r...

[RicardoRomero_1]

I've tried this code but I havent the class zcl_excel_template_data . I have a very old version of SAP. ABAP 701. I got the version of ABAP2xlsx by copying it from another project, and I had to spend a couple of weeks correcting code errors to get it to work in my version. So I don't have the new updates.

[abo]

argggh 😞 what a terrible situation 😞 I also have older systems so I occasionally run into problems but we normally fix it upstream, either directly or via downport changes

[Sandra_Rossi]

Maybe you can try the original solution before its integration in abap2xlsx, much less code to adapt to 7.01: https://community.sap.com/t5/application-development-and-automation-blog-posts/best-way-to-generate-...

[juan_suros]

Have you considered saving the VBA commands into a VBscript file, and then executing the script on the application server?

As you lay out this issue, you are executing this VBA code on a copy of Excel resident on your SAPGUI host PC. This copy of Excel has the XLSX template installed. Using OLE to call the instance of Excel is low performance, but it gives you great flexibility in the content of the commands, ranges in this case.

I have had great success in writing VBscript commands into a text file, saving it in the working directory defined by SAPGUI, and then executing it on the PC.

Inside the VBscript you can read any number of command line parameters in the WScript.Arguments object. Pass these parameters to the VBscript using the PARAMETER string in cl_gui_frontend_services=>execute.

This is a workaround, but the performance is vastly superior to OLE.

DATA: strcommand TYPE string.

  strcommand = |"{ w_path }{ 'Z_SCRIPT.vbs'(vbs) }"|
            && | "{ p_file }"|
            && | "{ p_tab }"|
            && | "{ w_path }"|.

* Run script on Win32 WorkStation
  CALL METHOD cl_gui_frontend_services=>execute
    EXPORTING
      application            = 'WScript'
      parameter              = strcommand
      synchronous            = 'X'
.

[RicardoRomero_1]

Thanks for the suggestion, although I think what you're proposing won't work in our scenario.

We need the file to be generated with code inside—it’s a file that will be dynamically filled out for each RFQ and sent to suppliers who have to complete it with their offers. Inside the Excel file, we have certain data validations, modules to display information, etc., so we need to use these downloaded XLSM files and then edit them to add the information in each case.

[Tomas_Buryanek]

Using cell ranges and copy/pasting data via the clipboard is really helpful to improve OLE performance.

But I do not see you using this approach in your example code. Why?

Example code (adjusting needed):

"Export itab data to clipboard
cl_gui_frontend_services=>clipboard_export( ... ).

"Cell 1
CALL METHOD OF lo_excel 'Cells' = lo_cell1
  EXPORTING
  #1 = 1 "Row
  #2 = 1. "Column

"Cell 2
CALL METHOD OF lo_excel 'Cells' = lo_cell2
  EXPORTING
  #1 = 1 "Row
  #2 = 1. "Column

"Create a range from two cells
CALL METHOD OF lo_excel 'Range' = lo_range
  EXPORTING
  #1 = lo_cell1
  #2 = lo_cell2.

"Paste data from clipboard to a Range
CALL METHOD OF lo_range 'Select'.
CALL METHOD OF lo_worksheet 'Paste'.

[RicardoRomero_1]

this is only an example, the problem is not writting the data, the problem is locking the cells, the second part of the code. when i try to select several ranges at once