CODEWITHSUNDEEP
excelabapole
baarkerlounger
baarkerlounger

Reputation: 1226

Multiple lines in same excel cell using OLE

I have some SAP OLE code that takes an internal table and pastes it into excel. However one field requires two lines within the same cell.

I've tried using CL_ABAP_CHAR_UTILITIES-NEWLINE and CL_ABAP_CHAR_UTILITIES-VERTICAL TAB and 'CR_LF' character as carriage return characters and these work fine when setting the value of the cell individually but using the paste method the start a new line (not a new line in the same cell). Unfortunately setting cell values manually is too performance intensive to be helpful.

I've also tried recording a macro of the alt + return key which shows me that excel reads that as ="line1" & char(10) & "line2" but I can't get this to parse properly either using OLE.

Is there any way I can make the paste method parse carriage return characters properly?

My current approach is below. Thanks for any help.

CONCATENATE line1 line2 INTO mult_lines SEPARATED BY cl_abap_char_utilities=>newline.

Then I add mult_lines to internal table and concatenate each line of the internal table into gt_data[] which is of type: LIKE Table OF gv_data(4096) TYPE c.

CALL METHOD cl_gui_frontend_services=>clipboard_export
IMPORTING
  data                 = gt_data[]
CHANGING
  rc                   = gv_rc
EXCEPTIONS
  cntl_error           = 1
  error_no_gui         = 2
  not_supported_by_gui = 3
  OTHERS               = 4.

CALL METHOD OF gv_appl 'Cells' = gv_cell
EXPORTING
#1 = lv_row1
#2 = lv_col1.

CALL METHOD OF gv_appl 'Range' = gv_range
EXPORTING
#1 = gv_cell
#2 = gv_cell.

CALL METHOD OF gv_range 'Select'.
CALL METHOD OF gv_sheet 'Paste'.

Upvotes: 1

Views: 7958

Answers (2)

zhaow
zhaow

Reputation: 83

Double quotation marks solve the problem. Just double quote all the text you wanna put into one cell, and have the cells connected with 0x09 (next column) or 0x0A (next row)

For example: (i'm using ASCII code to represent the chars) Pasting 0x41 0x0A 0x42 into a cell, we fill two adjacent rows, one line of text in each, 

A
B

while pasting 0x22 0x41 0x0A 0x42 0x22 into a cell, we fill one cell with 2 lines.

"A
B"

The example below fills to cells with 2 lines each:

CONSTANTS:
  nextC TYPE abap_char1 VALUE cl_abap_char_utilities=>horizontal_tab,
  nextR TYPE abap_char1 VALUE cl_abap_char_utilities=>newline,
  quot TYPE abap_char1 VALUE '"'.

DATA:
  buffer TYPE string.

CONCATENATE quot 'R1C1L1' nextR 'R1C1L2' quot nextC quot 'R1C2L1' nextR 'R1C2L2' quot INTO buffer.

Upvotes: 2

tomdemuyt
tomdemuyt

Reputation: 4592

This would work only cell per cell, but you can try to use Application.SendKeys "{F2}" which will open the edit bar and then do the paste of multiple lines. This should work, not sure I recommend this though. Why would you not simply set the value through setting the value of the cell?

Upvotes: 0

Related Questions