Add line break in comment in excel using ole2

[RicardoRomero_1]

Hello all.

I'm trying to add a comment in a cell in an excel sheet created with ole2 objects.
I know there is the abap2xlsx project, but our program is an old program in an old project, just trying to add some new features... the idea is not changing all the code in order to use abap2xlsx... 

I've managed to add a comment in a cell with this solution:
https://community.sap.com/t5/application-development-discussions/excel-cell-comments-via-ole-object/...

But I would like to add line breaks in the comment, and by the moment I can't...

I've created some macros in excel to see the VBA code and looks like this:

'Range("E10").Comment.Text Text:="asdasd" & Chr(10) & "asdsa" & Chr(10) & "" & Chr(10) & "asda" & Chr(10) & "" & Chr(10) & "" & Chr(10) & "asdadasdads" & Chr(10) & "" & Chr(10) & "asd"

I've tried to add these Chr(10) to add line breaks but doesnt work.

Tried something like this:

CALL METHOD OF obj_ecomment 'Text' = obj_etext
  EXPORTING
  #1 = '"Comments" & Chr(10) & "coments2"'.

But looks like this:

I've tried with some other vba constants with no success:
https://learn.microsoft.com/es-es/office/vba/language/reference/user-interface-help/miscellaneous-co...

Any tip ?

Thanks in advance.

[RicardoRomero_1]

Solved !

https://community.sap.com/t5/application-development-discussions/help-ineed-line-feed-charater-but-n...

CONSTANTS crlf(2) VALUE %_cr_lf.
DATA: lv_comment TYPE string.
CONCATENATE 'Line 1'
             crlf
            'Line 2'
             crlf
            'Line 3'
            INTO lv_comment
            SEPARATED BY space.

CALL METHOD OF obj_ecomment 'Text' = obj_etext
  EXPORTING
  #1 = lv_comment.

[Sandra_Rossi]

That is very old fashioned and not legible. Instead, use this (ABAP >= 7.40):

lv_comment = concat_lines_of( sep   = |\r\n|
                              table = VALUE string_table(
                                      ( `Line 1` )
                                      ( `Line 2` )
                                      ( `Line 3` ) ) ).

[RicardoRomero_1]

We are in SAP ECC 6.0, SAP_ABA 701...

[RicardoRomero_1]

Well... part two...

How to change the size of this comment??

In the macro I've created in excel the code is:

'Selection.ShapeRange.ScaleWidth 3.56, msoFalse, msoScaleFromTopLeft
'Selection.ShapeRange.ScaleHeight 3.81, msoFalse, msoScaleFromTopLeft

I've tried several things but no luck

REPORT zric_ole2.

TYPE-POOLS: soi,ole2.
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_worksheets    TYPE  ole2_object,
lo_column        TYPE  ole2_object,
lo_row           TYPE  ole2_object,
lo_cell          TYPE  ole2_object,
lo_font          TYPE ole2_object.

DATA: lo_cellstart      TYPE ole2_object,
lo_cellend        TYPE ole2_object,
lo_selection      TYPE ole2_object,
lo_validation     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.

*  CALL METHOD OF lo_application 'Worksheets' = lo_worksheet
*    EXPORTING #1 = 1.
*  CALL METHOD OF lo_worksheet 'Activate'.

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 Comments'.

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

DATA:
obj_ecomment     TYPE ole2_object,
obj_etext        TYPE ole2_object.

CALL METHOD OF lo_worksheet 'Range' = lo_range
  EXPORTING
  #1 = 'A1'.
*  #2 = 'A1'.

*CALL METHOD OF lo_cell 'AddComment'.
*CALL METHOD OF lo_cell 'Comment' = obj_ecomment.

CALL METHOD OF lo_range 'AddComment'.
CALL METHOD OF lo_range 'Comment' = obj_ecomment.

SET PROPERTY OF obj_ecomment 'Visible' = 1.

CONSTANTS crlf(2) VALUE %_cr_lf.
DATA: lv_comment TYPE string.
CONCATENATE 'Line 1'
             crlf
            'Line 2'
             crlf
            'Line 3'
            INTO lv_comment
            SEPARATED BY space.

CALL METHOD OF obj_ecomment 'Text' = obj_etext
  EXPORTING
  #1 = lv_comment.
*--------------------------------------------------------------------*


DATA: lo_shaperange TYPE ole2_object.
DATA: lo_shape      TYPE ole2_object.

*    Range("A1").Comment.Shape.Select True
*    Range("A1").Comment.Text Text:="Line 1 " & Chr(13) & "" & Chr(10) & " Line 2 " & Chr(13) & "" & Chr(10) & " Line 3"
*    Range("A1").Comment.Text Text:="Line 1 " & Chr(13) & "" & Chr(10) & " Line 2 " & Chr(13) & "" & Chr(10) & " Line 3"
*    Selection.ShapeRange.ScaleWidth 2.35, msoFalse, msoScaleFromTopLeft
*    Selection.ShapeRange.ScaleHeight 2.43, msoFalse, msoScaleFromTopLeft
*    Range("A1").Select
*
*
*
*    Range("A1").Comment.Shape.Select True
*    Range("A1").Comment.Text Text:="Line 1 " & Chr(13) & "" & Chr(10) & " Line 2 " & Chr(13) & "" & Chr(10) & " Line 3"
*    Selection.ShapeRange.ScaleWidth 1.53, msoFalse, msoScaleFromTopLeft
*    Selection.ShapeRange.ScaleHeight 1.7, msoFalse, msoScaleFromTopLeft
*    Selection.ShapeRange.ScaleHeight 1.42, msoFalse, msoScaleFromTopLeft
*    Selection.ShapeRange.ScaleWidth 1.55, msoFalse, msoScaleFromTopLeft
*    Selection.ShapeRange.ScaleHeight 1.33, msoFalse, msoScaleFromTopLeft
*    Selection.ShapeRange.IncrementLeft 91.5
*    Selection.ShapeRange.IncrementTop 45#

CALL METHOD OF obj_ecomment 'Shape' = lo_shape.
*CALL METHOD OF lo_shape 'Select' = lo_selection
* EXPORTING
*   #1 = 1.
GET PROPERTY OF lo_shape 'SELECTION' = lo_selection.

*CALL METHOD OF obj_ecomment 'Selection' = lo_selection.
GET PROPERTY OF lo_selection 'ShapeRange' = lo_shaperange.
SET PROPERTY OF lo_shaperange 'ScaleWidth' = 5.
SET PROPERTY OF lo_shaperange 'ScaleHeight' = 5.
*--------------------------------------------------------------------*


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.

[RicardoRomero_1]

Part two... solved !

DATA: lo_shaperange TYPE ole2_object.
DATA: lo_shape      TYPE ole2_object,
      lo_text_frame TYPE ole2_object.

CALL METHOD OF obj_ecomment 'Shape' = lo_shape.
CALL METHOD OF lo_shape 'TextFrame' = lo_text_frame.
SET PROPERTY OF  lo_text_frame 'AutoSize' = 1.
SET PROPERTY OF  lo_shape 'Width' = 200.
SET PROPERTY OF  lo_shape 'Height' = 300.

[Sandra_Rossi]

You said "abap2xlsx project, but this is an old program in an old project". You didn't search well. I guess you're looking at the wrong place. Here it is: https://github.com/abap2xlsx

[Sandra_Rossi]

abap2xlsx is still actively maintained.

With abap2xlsx (https://github.com/abap2xlsx/demos/blob/e714a4d68548c1f3bfef6f0f28fdba6f1d395eb6/src/zdemo_excel_com...) :

  lo_comment = lo_excel->add_new_comment( ).
  CONCATENATE 'A comment split' cl_abap_char_utilities=>cr_lf 'on 2 lines?' INTO lv_comment.
  lo_comment->set_text( ip_ref = 'F6' ip_text = lv_comment ).

[RicardoRomero_1]

Sorry, I didn't explain myself well. I wanted to say that MY project is an old project.

I am enhancing a solution that we already have for downloading Excels

[RicardoRomero_1]

Sorry, I didn't explain myself well. I wanted to say that MY project is an old project.

I am enhancing a solution that we already have for downloading Excels

[Sandra_Rossi]

oh okay thanks for clarifying

[RicardoRomero_1]

EDIT.