Skip to Content

Spot instead comma in Excel from SAP

Hi all.

I get a Excel file from SAP. One of the fields is material, with format XXXXX.X in SAP, but in Excel it is shown with format XXXXX,X I need in Excel the material is shown with a spot, like SAP. What can I do?

The code is:

CREATE OBJECT Excel 'EXCEL.APLICATION'.

CALL METHOD OF Excel 'WORKBOOKS' = books.

CALL METHOD OF books 'ADD' = book.

LOOP AT tab.

PERFORM rellenar_celdas USING x1 tab-gltri.

PERFORM rellenar_celdas USING x2 tab-matnr.

....

ENDLOOP.

SET PROPERTY OF Excel 'VISIBLE' = 1.

---------------------------------------------------------------

FORM rellenar_celdas USING i j val.

CALL METHOD OF Excel 'CELLS' = cell

EXPORTING

#1 = i

#2 = j

SET PROPERTY OF cell 'VALUE' = val.

ENFORM.

Thanks a lot.

Marta.

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

5 Answers

  • Best Answer
    Sep 22, 2014 at 09:35 AM

    Hi

    In the download data try and add extra " in front of the material .

    Regards.

    Add comment
    10|10000 characters needed characters exceeded

  • Sep 22, 2014 at 09:17 AM

    Hi all.

    I have to write in Excel, materials like 8987654.6 and in Excel it is shown as 8987654,6 (comma inside spot). In program, variable is defined as CHAR18. I have done some tests:

    - If I add letters as AA8987654.6 in Excel the material is shown right: AA8987654.6

    - If I put the material "8987654.6", Excel shows the material "8987654.6", it's wrong.

    - If I put the material '8987654.6', Excel shows the material with comma 8987654,6

    How can I put matnr as a text?

    Thanks a lot.

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi, Marta!

      I guess you have some Excel template which is filled with data. You can set the cell format manually or in a macro (as Sergey suggested).

      P.S. Adding single quote as Eitan suggested will also make Excel treat the data as text.

  • avatar image
    Former Member
    Sep 18, 2014 at 01:21 PM

    Hi, Marta!

    Possibly EXCEL regards matnr field as a number and uses the separator which is set in Windows locale or in EXCEL settings.

    Try to upload matnr as a text.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Sep 18, 2014 at 01:22 PM

    I did like this


    FORM CREATE_GRID USING

    X1 TYPE I Y1 TYPE I

    X2 TYPE I Y2 TYPE I

    NAME.

    *Workbooks.workSheets(1).columns(GS_CELLS).

    *Worksheets("Sheet1").Cells(5, 3).Font.Size = 14

    CALL METHOD OF GS_EXCEL 'Cells' = GS_CELL1

    EXPORTING #1 = X1 #2 = Y1.

    CALL METHOD OF GS_EXCEL 'Cells' = GS_CELL2

    EXPORTING #1 = X2 #2 = Y2.

    CALL METHOD OF GS_EXCEL 'Range' = GS_CELLS

    EXPORTING #1 = GS_CELL1 #2 = GS_CELL2.

    CALL METHOD OF GS_CELLS 'Select' .

    CALL METHOD OF GS_CELLS 'Merge' .

    CALL METHOD OF GS_CELLS 'BorderAround'

    EXPORTING #1 = 1 #2 = 2.

    SET PROPERTY OF GS_CELLS 'NumberFormat' = '#,##0.00' .

    SET PROPERTY OF GS_CELLS 'RowHeight' = 13.

    SET PROPERTY OF GS_CELLS 'Value' = NAME.

    SET PROPERTY OF GS_CELLS 'VerticalAlignment' = 2.

    * SET PROPERTY OF gs_cells 'HorizontalAlignment' = -4108.

    SET PROPERTY OF GS_CELLS 'WrapText' = 1.

    GET PROPERTY OF GS_CELLS 'Font' = GS_FONT.

    SET PROPERTY OF GS_FONT 'Size' = 8.

    ENDFORM.

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 15, 2014 at 07:40 AM

    Add a ' in front of the number, p.e., '89879.8 and in the Excel the variable is written as a text.

    Thanks a lot!!

    Marta.

    Add comment
    10|10000 characters needed characters exceeded