Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Truncation of leading Zeros when Down Loading into Excel - OLE Objects

Former Member
0 Kudos

Hi,

Can any one help me on this.

I am using <b>OLE Objects</b> to download Data into Excel Sheet. Data with leading Zeros is getting truncated in Excel.

Ex: Report Output is showing Plant Number as 0002. But when i am downloading to Excel Plant value will become 2 .

I would like to have it as 0002 in Excel.

I have declared Werks as CHAR of 4.I am using OLE Obects for Downloading into Excel Sheet.

I am using "OLE2_OBJECT" I can not use any other FMs to down load to Excel.As i am modifying this program not creating.

Thanks In Advance.

K.Nirmala

Message was edited by: Nirmala Reddy

6 REPLIES 6

Former Member
0 Kudos

Hi Nirmala,

While downloading to EXCEL are you giving file type anywhere? bcoz i dont have any idea on OLE

If u r using filetype , give that as DBF

Former Member
0 Kudos

Hi Nirmala,

Can I know which OLE object you are using?

Anyway, you can achieve it using ws_download with file type = 'WK1'.

  1. Roopesh Singh

Former Member
0 Kudos

Welcome to SDN.

Use GUI_DOWNLOAD function module and give the file type as 'DAT'.

Or

While opening the excel file. Open a Empty excel sheet -> Click on the folders button -> select the excel u have download -> It will take u to Text import Wizard -> select Delimited radio button -> Next -> Select all the columns and select Text Radio button and Press finish.

Regards,

Prakash.

guillaume-hrc
Active Contributor
0 Kudos

Hi,

This is not truncated, it is the way Excel behaves when dealing with numerical values.

If you want to keep the way SAP displays it, you should either:

1) Add a leading simple quote in the cell, before your plant number

2) Try to change the cell format and applying a specific format (like '####').

Best regards,

Guillaume

Former Member
0 Kudos

Hi Nirmala,

While downloading to excel sheet, u need to change the number format of cell from General to Text, then leading zero's won't get deleted. For that u need to set the property of the cell. Please check this sample code,

INCLUDE OLE2INCL.

tables : zobrent.

data : it_kna1 type table of zobrent with header line.

  • handles for OLE objects

DATA: H_EXCEL TYPE OLE2_OBJECT, " Excel object

H_MAPL TYPE OLE2_OBJECT, " list of workbooks

H_MAP TYPE OLE2_OBJECT, " workbook

H_ZL TYPE OLE2_OBJECT, " cell

H_F TYPE OLE2_OBJECT. " font

DATA H TYPE I.

DATA: cell1 TYPE ole2_object.

*&----


*& Event START-OF-SELECTION

*&----


START-OF-SELECTION.

select * from zobrent into table it_kna1

where zopanid = '10001'

and zo_brent = '050'.

  • start Excel

CREATE OBJECT H_EXCEL 'EXCEL.APPLICATION'.

PERFORM ERR_HDL.

SET PROPERTY OF H_EXCEL 'Visible' = 1.

  • get list of workbooks, initially empty

CALL METHOD OF H_EXCEL 'Workbooks' = H_MAPL.

PERFORM ERR_HDL.

  • add a new workbook

CALL METHOD OF H_MAPL 'Add' = H_MAP.

PERFORM ERR_HDL.

  • output column headings to active Excel sheet

PERFORM FILL_CELL USING 1 1 1 'EDate'.

PERFORM FILL_CELL USING 1 2 1 'Brent'.

PERFORM FILL_CELL USING 1 3 1 'Zopanid'.

PERFORM FILL_CELL USING 1 4 1 'Contract Type'.

PERFORM FILL_CELL USING 1 5 1 'Price Type'.

PERFORM FILL_CELL USING 1 6 1 'Installation Type'.

PERFORM FILL_CELL USING 1 7 1 'Volume'.

PERFORM FILL_CELL USING 1 8 1 'AQ'.

PERFORM FILL_CELL USING 1 9 1 '00000123'.

LOOP AT IT_KNA1.

  • copy values to active EXCEL sheet

H = SY-TABIX + 1.

PERFORM FILL_CELL USING H 1 0 IT_KNA1-zo_effdat.

PERFORM FILL_CELL USING H 2 0 IT_KNA1-zo_brent.

PERFORM FILL_CELL USING H 3 0 IT_KNA1-zopanid.

ENDLOOP.

CALL METHOD OF h_excel 'Cells' = cell1

EXPORTING

#1 = 1

#2 = 1.

FREE OBJECT H_EXCEL.

PERFORM ERR_HDL.

if sy-subrc eq 0.

write : / 'year'(001).

endif.

*----


  • FORM FILL_CELL

*----


  • sets cell at coordinates i,j to value val boldtype bold

*----


FORM FILL_CELL USING I J BOLD VAL.

CALL METHOD OF H_EXCEL 'Cells' = H_ZL EXPORTING #1 = I #2 = J.

PERFORM ERR_HDL.

GET PROPERTY OF H_ZL 'Font' = H_F.

PERFORM ERR_HDL.

SET PROPERTY OF H_F 'Bold' = BOLD .

PERFORM ERR_HDL.

***Changing the format of the cell from General to Text

<b>SET PROPERTY OF H_ZL 'NumberFormat' = '@'.</b>

PERFORM ERR_HDL.

SET PROPERTY OF H_ZL 'Value' = VAL .

PERFORM ERR_HDL.

ENDFORM.

*&----


*& Form ERR_HDL

*&----


FORM ERR_HDL.

IF SY-SUBRC <> 0.

WRITE: / 'Fehler bei OLE-Automation:'(010), SY-SUBRC.

STOP.

ENDIF.

ENDFORM. " ERR_HDL

U just paste this code in a sample program & see.

Please reward, if found helpful.

Former Member
0 Kudos

chk this link , if it can help u

<a href="https://www.sdn.sap.comhttp://www.sdn.sap.comhttp://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/docs/library/uuid/c1d54348-0601-0010-3e98-bd2a2dcd9e6c">Easy Reference to OLe</a>