topic Re: Excel in Application Development and Automation Discussions

Excel

Former Member — Tue, 18 Sep 2007 09:42:21 GMT

Hello masters!!

Im encountering a problem when I download a file in excel. Actually I dont know if its abap side or excel thing.

the problem is I have a description field example of text in description field.

example ::

color blue #house

what happen is every description that has a number sign in text .

the text after the number is being place to the next row of the excel.

which is wrong.

Do you have any idea on how to solve this?

this is what happen in my excel.

row 1 color blue

row 2 house.

that's wrong I need to have a text in just one row/cell.

Do you have any idea on ho to correct this?

Re: Excel

Former Member — Tue, 18 Sep 2007 09:45:16 GMT

hi,

just enlarge the size of the excel cell .. and check...

Reply back

With Rgds,

S.Barani

Re: Excel

messier31 — Tue, 18 Sep 2007 09:49:27 GMT

Hi,

Check your code where you are created trying to concatenate fields i.e columns by tab i.e hex 09.

Enjoy SAP.

Pankaj Singh.

Re: Excel

Former Member — Tue, 18 Sep 2007 09:49:33 GMT

nothing happens.I still got the same error.

what I mean is

example::

description

color blue #house

what happens in excel:

row 1 color blue

row 2 house

the text after the number sign proceeds to the next row not in the next column.

I need to have a complete text in just one row cell.

expected:

color blue #house

Thanks , hope you can help me

Re: Excel

Former Member — Tue, 18 Sep 2007 09:53:26 GMT

hi Salma,

can u paste the code here.....

With Rgds,

S.Barani

Re: Excel

Former Member — Tue, 18 Sep 2007 09:54:02 GMT

Master ,

I didnt do any concatenation in that field. I just fetch the whole value of description from its table.It just so happen that the value of description field

is 'color blue #house' I need to have the same text in the corresponding row cell of that value. What happen is after the text in number sign it proceeds to the next row w/c is wrong.

I dont have any idea why it happens....im clueless..

Re: Excel

Former Member — Tue, 18 Sep 2007 09:57:55 GMT

here's my code in the form where I fetch my data::

FORM get_data .

DATA: lv_pr(1) TYPE c,

lv_rfq(1) TYPE c,

lv_po(1) TYPE c,

lv_sa(1) TYPE c,

lv_sf(1) TYPE c,

lv_cont(1) TYPE c.

SELECT banfn bnfpo loekz statu

ekgrp erdat txz01 matnr

werks lgort matkl menge

meins badat lfdat frgdt

preis peinh pstyp knttp

lifnr flief konnr ktpnr

infnr qunum qupos ebeln

ebelp bedat bsmng xoblr rsnum

FROM eban

INTO TABLE gt_eban

WHERE banfn IN s_banfn

AND bnfpo IN s_bnfpo

AND loekz IN s_loekz

AND statu IN s_statu

AND ekgrp IN s_ekgrp

AND erdat IN s_erdat

AND matnr IN s_matnr

AND werks IN s_werks

AND lgort IN s_lgort

AND matkl IN s_matkl

AND badat IN s_badat

AND lfdat IN s_lfdat

AND pstyp IN s_pstyp

AND knttp IN s_knttp

AND konnr IN s_konnr

AND ktpnr IN s_ktpnr.

IF sy-subrc = 0.

SORT gt_eban BY banfn frgdt ASCENDING.

DELETE ADJACENT DUPLICATES FROM gt_eban COMPARING banfn.

ENDIF.

SELECT lifnr

name1

FROM lfa1

INTO TABLE gt_lfa1

FOR ALL ENTRIES IN gt_eban

WHERE lifnr = gt_eban-lifnr.

IF sy-subrc = 0.

SORT gt_lfa1 BY lifnr ASCENDING.

ENDIF.

SELECT lifnr

name1

FROM lfa1

INTO TABLE gt_lfa2

FOR ALL ENTRIES IN gt_eban

WHERE lifnr = gt_eban-flief.

IF sy-subrc = 0.

SORT gt_lfa2 BY lifnr ASCENDING.

ENDIF.

SELECT ekgrp

eknam

FROM t024

INTO TABLE gt_t024

FOR ALL ENTRIES IN gt_eban

WHERE ekgrp = gt_eban-ekgrp.

IF sy-subrc = 0.

SORT gt_t024 BY ekgrp ASCENDING.

ENDIF.

CALL FUNCTION 'MONTH_NAMES_GET'

EXPORTING

language = sy-langu

TABLES

month_names = gt_table

EXCEPTIONS

month_names_not_found = 1

OTHERS = 2.

IF sy-subrc <> 0.

MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno

WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.

ENDIF.

LOOP AT gt_eban INTO wa_eban.

MOVE-CORRESPONDING wa_eban TO wa_output.

IF wa_eban-statu = 'N'.

lv_pr = '1'.

CONCATENATE lv_pr 'PR' INTO gv_statu.

ELSEIF wa_eban-statu = 'A'.

lv_rfq = '2'.

CONCATENATE lv_rfq 'RFQ' INTO gv_statu.

ELSEIF wa_eban-statu = 'B'.

lv_po = '3'.

CONCATENATE lv_po 'PO' INTO gv_statu.

ELSEIF wa_eban-statu = 'K'.

lv_cont = '4'.

CONCATENATE lv_cont 'CONT' INTO gv_statu.

ELSEIF wa_eban-statu = 'L'.

lv_sa = '5'.

CONCATENATE lv_sa 'SA' INTO gv_statu.

ELSEIF wa_eban-statu = 'S'.

lv_sf = '6'.

CONCATENATE lv_sf 'SF' INTO gv_statu.

ENDIF.

CALL FUNCTION 'HR_SGPBS_YRS_MTHS_DAYS' "PR TO PO Process Days

EXPORTING

beg_da = wa_eban-frgdt

end_da = wa_eban-bedat

IMPORTING

no_cal_day = gv_pdays

EXCEPTIONS

dateint_error = 1

OTHERS = 2.

IF sy-subrc <> 0.

MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno

WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.

ENDIF.

IF wa_eban-bedat = '00000000'.

gv_pdays ='0'.

ENDIF.

gv_pquantity = wa_eban-menge - wa_eban-bsmng. "PR Balance Quantity

MOVE: gv_pdays TO wa_output-pdays,

gv_pquantity TO wa_output-pquantity,

gv_statu TO wa_output-pstatu.

READ TABLE gt_lfa1 INTO wa_lfa1

WITH TABLE KEY lifnr = wa_eban-lifnr.

IF sy-subrc = 0.

MOVE wa_lfa1-name1 TO wa_output-name1.

ENDIF.

READ TABLE gt_lfa2 INTO wa_lfa2

WITH TABLE KEY lifnr = wa_eban-flief.

IF sy-subrc = 0.

MOVE wa_lfa2-name1 TO wa_output-name2.

ENDIF.

READ TABLE gt_t024 INTO wa_t024

WITH TABLE KEY ekgrp = wa_eban-ekgrp.

IF sy-subrc = 0.

MOVE wa_t024-eknam TO wa_output-eknam.

ENDIF.

APPEND wa_output TO gt_output.

CLEAR: gv_statu ,

gv_pdays ,

gv_pquantity ,

wa_output-name1,

wa_output-name2.

ENDLOOP.

ENDFORM. " get_data

txz01 is the field im referring to. sorry masters , I dont know w/c part of my code do i need to show you cause that's the only thing I did to retrieve my data(txz01). I didnt do any other coding for txz01(description) field except that.

Re: Excel

former_member189059 — Tue, 18 Sep 2007 10:01:50 GMT

Hello salma,

I can tell you the reason why it is coming in the next line

the # character is represented as a 'new line' character by excel and hence it puts the data in the next row

try replacing that character in SAP itself

use the 'replace all occurrences' code

Re: Excel

Former Member — Tue, 18 Sep 2007 10:07:48 GMT

Yeah I also consider that idea but I was wondering why the other text that contains number sign '#' displayed properly. Question master If I use replace all occurences would it literally replace the symbol '#' meaning it will totally be vanished from my text??

would it be like this in my excel ? "color blue house" am I right?? the '#' will be deleted??

Re: Excel

former_member189059 — Tue, 18 Sep 2007 11:21:52 GMT

yes it will come as 'color blue house' and the # would be totally removed by this method

can u please tell me which function you are using to download the data

I tried using GUI_DOWNLOAD and the data came properly

(my system is ECC5 and Excel 2000)

Re: Excel

Former Member — Wed, 19 Sep 2007 00:47:10 GMT

thats also my concern. I was wondering why the other text w/c also contains '#' character displayrd properply and other text didnt. I also use gui_download.

Anyways , I cant delete the '#' character because thats relevant in the context.

Do you have any idea on how i can separate or give at least one space the '#' character to its left side character.

I mean example::

color blue#house.(no space)

I want it to be like this::

color blue# house((with space between '#' and house)

Many thanks!!

Re: Excel

former_member189059 — Wed, 19 Sep 2007 03:05:09 GMT

Hi Salma,

try this code

data old type string value 'color blue#house'.
data new type string.
data pos like sy-fdpos.

search old for '#'.
if sy-subrc = 0.
  pos = sy-fdpos + 1.
endif.

concatenate old(pos) old+pos into new separated by space.
write:/ new.