Help! Ineed Line Feed charater but no need Carriage Return

[Former Member]

Hi ABAP Guru,

I have a problem and I need your help with writting data into Excel cells using OLE Technique, I have to insert multiple lines of text into sheet header text.

I've record excel macro and got the code below..

With ActiveSheet.PageSetup

blah blah blah..

.CenterHeader = "HEADER LINE 1" & Chr(10) & "HEADER LINE 2" & Chr(10) & "HEADER LINE 3"

blah blah blah..

Endwith

in the line .CenterHeader = "HEADER LINE 1" & Chr(10) & "HEADER LINE 2" & Chr(10) & "HEADER LINE 3"

means they will have 3 lines in excel sheet header text..

my problem is I can't see any character to use as Chr(10) (in this VB Code) for ABAP..

I already have a look at class CL_ABAP_CHAR_UTILITIES but 'NEWLINE' character not worked, And 'CR_LF' character not worked too.

and I can't use variable Type 'X' to contain value '0A' , because i need to concatenate text with this special characters for the line feed, but ABAP is not allow the 'X' var to used in CONCATENET.

Please give me your advice

Thanks in advanced

Nattapash C.

[Former Member]

Hi Nattapash Changpant,

try to use one of the VBA-Constants:

vbBack
vbCr
vbCrLf
vbFormFeed
vbLf
vbNewLine
vbTab
vbVerticalTab

Regards

REA

[Former Member]

Hi, Ramy EL-ARNAOUTY

Thank you very much for your reply, But I've a little bit confusing.

Did you mean using VBA-Constant in ABAP program? and Could you explain how to use?

Because now I need to coding in ABAP to write some special character for newline to Excel header text. But in this case I can't use some attribute of ABAP Class 'CL_ABAP_CHAR_UTILITIES'.

So I need help for finding some special character in ABAP for use as newline character in excel.

Best Regard,

Nattapash C.

[Former Member]

Hi Nattapash Changpant,

i thought you need to create a string by using concatenate,

in order to assign it to ActiveSheet.PageSetup.CenterHeader

in your excel macro.Therfore you can use VBA-Constants in

your String.

Regards

REA

[Former Member]

Hi Nattapash Changpant ,

>

 So I need help for finding some special character in ABAP for use as newline character in excel.

I dont understand why you need to do this? You just have to build a string in ABAP that looks like VBA-Code.

Please post your source code and try to explain again.

Regards

REA

[Former Member]

Hi, Ramy EL-ARNAOUTY

Thanks for your reply again. ^ ^

Now I think I'll try what I was suggested from you, that mean I'll create string like VBA-Script.

and what's the next after I've finish concatenete the text?

Assign the string into excel macro ? How?

Like the below code ? (I copy from another thread).

CREATE OBJECT h_excel 'EXCEL.APPLICATION'.
Call Method of h_excel 'WORKBOOKS' = h_mapl.
set property of h_excel 'VISIBLE' = 0.
Call Method of h_mapl 'OPEN'
EXPORTING
#1 = d_file.
CALL METHOD OF H_EXCEL 'ActiveWorkbook' = h_book .
CALL METHOD OF H_book 'Activesheet' = H_sheet .
 
CALL METHOD OF h_excel 'RUN'
EXPORTING
#1 = ld_macro.

Many Thanks.

Nattapash C.

Edited by: Nattapash Changpant on Jul 21, 2009 2:40 PM

[Former Member]

Hi Nattapash Changpant,

i've written a report that sets CenterHeader value of ActiveSheets PageSetup:

REPORT  z_excel_ole.

TYPE-POOLS ole2.

DATA: excel TYPE ole2_object, workbook  TYPE ole2_object,
      sheet TYPE ole2_object, pagesetup TYPE ole2_object.

DATA  value(15) TYPE c.

* Create Excel Application
CREATE OBJECT excel 'Excel.Application'.

* Set visibility of Application
SET PROPERTY OF excel 'VISIBLE' = 0. "0 = hidden, 1 = visible

* Add new Workbook
CALL METHOD OF excel 'WORKBOOKS' = workbook.
CALL METHOD OF workbook 'ADD'.

* Get geference to Workbook
GET PROPERTY OF excel 'ACTIVEWORKBOOK' = workbook.

* Get geference to Worksheet
GET PROPERTY OF excel 'ACTIVESHEET' = sheet.

* Get geference to Sheets Pagesetup
GET PROPERTY OF sheet 'PAGESETUP' = pagesetup.

* Move value to Property CenterHeader of Pagesetup
* Replace content of value by your needs
value = 'Hello World!'. 
SET PROPERTY OF pagesetup 'CenterHeader' = value.

* Save Workbook
CALL METHOD OF workbook 'SAVEAS'
  EXPORTING #1 = 'C:\TMP\an_excel_file.xls'.

* Leave Application
CALL METHOD OF excel 'QUIT'.

FREE OBJECT: pagesetup, sheet, workbook, excel.

If you need to write reports that control external apps via OLE,

you should get familiar with SAP Table OLELAOD (OBJTYPE = EXCEL.APP for Excel OLEs).

Please feedback if helpful or not.

Regards

REA

[Former Member]

please excuse, i've forgotten your linefeed problem...

declare constant

CONSTANTS crlf(2) VALUE %_cr_lf.

that create string for CenterHeader and move it to value:

CONCATENATE 'Line 1'
             crlf
            'Line 2'
             crlf
            'Line 3'
            INTO value
            SEPARATED BY space.

Regards

REA

[Former Member]

Finally - I managed to Do it.

If you want the string to be in same cell - but alt+enter separted then keep the String in Quotes - "String".

REPLACE ALL OCCURRENCES OF CL_ABAP_CHAR_UTILITIES=>NEWLINE

in p_text

WITH CL_ABAP_CHAR_UTILITIES=>CR_LF.

CONCATENATE '"'

p_text

'"'

INTO p_text.

It worked for me. ALL THE BEST.

[Former Member]

Hi Nattapash,

i've provided solution to your problem,

you did not feedback

plus

you did mark thread as answered.

Your behaviuor is very rude!

Regards

REA

[Former Member]

Hi nnandrajog,

Thank you very much

Nattapash C.

[Former Member]

Hi Ramy EL-ARNAOUTY ,

I'm so sorry for that. I'm very busy rigth now.

But thank you very much for your help. I just try your code and and it seems work!.

Thank you very much!!.

Nattapash C.

[Former Member]

>

> Hi Ramy EL-ARNAOUTY ,
> 
> I'm so sorry for that. I'm very busy rigth now.
> But thank you very much for your help. I just try your code and and it seems work!.
> 
> Thank you very much!!.
> Nattapash C.

If his suggestion solved your problem, why not give him the points he deserves?

Rob