Table control to Excel

Former Member — Mon, 12 Nov 2007 10:25:04 GMT

Hi,

I working with table control.I have one option to download the tablecontrol data to local excel file.So i using function module MS_EXCEL_OLE_STANDARD_DAT.Its working fine.But some material starts with 0.If i download that into excel file its ignore the 0.(Ex 0025 is displayed 25 in excel)

My requrement is i have to download the data into excel file what i have in table control. (Including start with 0)

Kindly help me to acheive this....

Regards,

K.Sudhakaran.

Re: Table control to Excel

JozsefSzikszai — Mon, 12 Nov 2007 10:31:09 GMT

hi Sudha,

I am afraid you can only do this, if you don't use the FM, but code the whole Excel download manually (using the OLE methods). Than you can set the Excel cells as text type and you can display with leading zeros.

On the other hand there is something I don't understand: Where do you save these material numbers in SAP? Because if these are real material numbers (I mean they are in MARA) AND they don't contain any letter (only numbers) than the leading zeros are truncated by SAP as well (because of the conversion exit on MATNR).

Re: Table control to Excel

Former Member — Mon, 12 Nov 2007 10:36:16 GMT

Hi,

Before Call your Function Module Do this.

1. Declare Your Parameter data type as Char(2).

2. Call this FM and pass your Parameter. For Ex Your Parameter is "Temp" Means,

CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT'

EXPORTING

input = Temp

IMPORTING

OUTPUT = Temp

Regards,

Omkar.

Re: Table control to Excel

mahaboob_pathan — Mon, 12 Nov 2007 10:39:35 GMT

hi,

check this code once

may be it's useful.

&----

*& Report ZBDC_TAB_CTRL_FB50 *

*& *

&----

*& *

&----

REPORT ZBDC_TAB_CTRL_FB50 .

DATA: I_BDCDATA LIKE BDCDATA OCCURS 0 WITH HEADER LINE.

****Header Internal Table

DATA: BEGIN OF T_HEAD OCCURS 0,

BLDAT(10) TYPE C,

BUDAT(10) TYPE C,

END OF T_HEAD.

****Detail Internal Table

DATA: BEGIN OF T_DET OCCURS 0,

BLDAT(10) TYPE C,

BUDAT(10) TYPE C,

HKONT LIKE ACGL_ITEM-HKONT,

SHKZG LIKE ACGL_ITEM-SHKZG,

WRBTR(16) TYPE C,

END OF T_DET.

DATA: T_EXCEL LIKE ALSMEX_TABLINE OCCURS 0 WITH HEADER LINE,

IDX(2) TYPE C,

FNAM(20) TYPE C,

FVAL(40) TYPE C.

***Internal Table for Holding Messages

DATA: BEGIN OF MSGTAB OCCURS 0.

INCLUDE STRUCTURE BDCMSGCOLL.

DATA: END OF MSGTAB.

Parameter for locating the .xls file

PARAMETER:P_FILE TYPE RLGRAP-FILENAME OBLIGATORY.

***Selection Block for Header Section

SELECTION-SCREEN : BEGIN OF BLOCK BLK1 WITH FRAME TITLE TEXT-001.

PARAMETER: PBROW_HD TYPE I,

PEROW_HD TYPE I,

PBCOL_HD TYPE I,

PECOL_HD TYPE I.

SELECTION-SCREEN : END OF BLOCK BLK1.

***Selection Block for Detail Section

SELECTION-SCREEN : BEGIN OF BLOCK BLK2 WITH FRAME TITLE TEXT-002.

PARAMETER: PBROW_DT TYPE I,

PEROW_DT TYPE I,

PBCOL_DT TYPE I,

PECOL_DT TYPE I.

SELECTION-SCREEN : END OF BLOCK BLK2.

****Selecting the

AT SELECTION-SCREEN ON VALUE-REQUEST FOR P_FILE.

CALL FUNCTION 'F4_FILENAME'

EXPORTING
PROGRAM_NAME = SYST-CPROG
DYNPRO_NUMBER = SYST-DYNNR
FIELD_NAME = ' '

IMPORTING

FILE_NAME = P_FILE.

START-OF-SELECTION.

***Selecting data from .xls for the header data

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

FILENAME = P_FILE

I_BEGIN_COL = PBCOL_HD

I_BEGIN_ROW = PBROW_HD

I_END_COL = PECOL_HD

I_END_ROW = PEROW_HD

TABLES

INTERN = T_EXCEL

EXCEPTIONS

INCONSISTENT_PARAMETERS = 1

UPLOAD_OLE = 2

OTHERS = 3.

IF SY-SUBRC <> 0.

MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

ENDIF.

****For Header internal Table

LOOP AT T_EXCEL WHERE ROW GT 0.

CASE T_EXCEL-COL.

WHEN 1. MOVE T_EXCEL-VALUE TO T_HEAD-BLDAT.

WHEN 2. MOVE T_EXCEL-VALUE TO T_HEAD-BUDAT.

ENDCASE.

AT END OF ROW.

APPEND T_HEAD.

CLEAR T_HEAD.

ENDAT.

ENDLOOP.

***Selecting data from .xls for the detail data

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

FILENAME = P_FILE

I_BEGIN_COL = PBCOL_DT

I_BEGIN_ROW = PBROW_DT

I_END_COL = PECOL_DT

I_END_ROW = PEROW_DT

TABLES

INTERN = T_EXCEL

EXCEPTIONS

INCONSISTENT_PARAMETERS = 1

UPLOAD_OLE = 2

OTHERS = 3.

IF SY-SUBRC <> 0.

MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

ENDIF.

****For Detail internal Table

LOOP AT T_EXCEL WHERE ROW GT 0.

CASE T_EXCEL-COL.

WHEN 1. MOVE T_EXCEL-VALUE TO T_DET-BLDAT.

WHEN 2. MOVE T_EXCEL-VALUE TO T_DET-BUDAT.

WHEN 3. MOVE T_EXCEL-VALUE TO T_DET-HKONT.

WHEN 4. MOVE T_EXCEL-VALUE TO T_DET-SHKZG.

WHEN 5. MOVE T_EXCEL-VALUE TO T_DET-WRBTR.

ENDCASE.

AT END OF ROW.

APPEND T_DET.

CLEAR T_DET.

ENDAT.

ENDLOOP.

***Populating i_bdcdata from the header internal table

LOOP AT T_HEAD .

PERFORM BDC USING 'H'.

***Populating i_bdcdata from the detail internal table

LOOP AT T_DET WHERE BLDAT = T_HEAD-BLDAT AND

BUDAT = T_HEAD-BUDAT.

PERFORM BDC USING 'D'.

ENDLOOP.

PERFORM POPULATE_BDCTABLE USING:'' 'BDC_OKCODE' '=BU'.

CALL TRANSACTION 'FB50' USING I_BDCDATA

MODE 'A'

UPDATE 'A'.

ENDLOOP.

&----

*& Form POPULATE_BDCTABLE

&----

text

----

-->P_0011 text
-->P_0012 text
-->P_0013 text

----

FORM POPULATE_BDCTABLE USING VALUE(P_FLAG)

VALUE(P_FNAM)

VALUE(P_FVAL).

IF P_FLAG = 'X'.

I_BDCDATA-PROGRAM = P_FNAM.

I_BDCDATA-DYNPRO = P_FVAL.

I_BDCDATA-DYNBEGIN = 'X'.

ELSE.

I_BDCDATA-FNAM = P_FNAM.

I_BDCDATA-FVAL = P_FVAL.

ENDIF.

APPEND I_BDCDATA.

CLEAR I_BDCDATA.

ENDFORM. " POPULATE_BDCTABLE

&----

*& Form BDC

&----

text

----

-->P_0416 text

----

FORM BDC USING VALUE(P_VAL).

****For Header

IF P_VAL = 'H'.

DATA:L_BLDAT(10) TYPE C,

L_BUDAT(10) TYPE C.

CLEAR:L_BLDAT,L_BUDAT.

WRITE: T_HEAD-BLDAT TO L_BLDAT,

T_HEAD-BUDAT TO L_BUDAT.

PERFORM POPULATE_BDCTABLE USING:'X' 'SAPMF05A' '1001',

' ' 'BDC_OKCODE' '=0006',

' ' 'BDC_CURSOR' 'ACGL_ITEM-WRBTR(01)',

' ' 'ACGL_HEAD-BLDAT' L_BLDAT,

' ' 'ACGL_HEAD-BUDAT' L_BUDAT .

****For Detail

ELSEIF P_VAL = 'D'.

IDX = IDX + 1.

IF IDX = 1.

CONCATENATE 'ACGL_ITEM-HKONT(0' IDX ')' INTO FNAM.

PERFORM POPULATE_BDCTABLE USING: ' ' FNAM T_DET-HKONT.

CONCATENATE 'ACGL_ITEM-SHKZG(0' IDX ')' INTO FNAM.

PERFORM POPULATE_BDCTABLE USING ' ' FNAM T_DET-SHKZG.

CONCATENATE 'ACGL_ITEM-WRBTR(0' IDX ')' INTO FNAM.

PERFORM POPULATE_BDCTABLE USING ' ' FNAM T_DET-WRBTR.

ELSE.

IF IDX > 10.

IDX = 10.

ENDIF.

PERFORM POPULATE_BDCTABLE USING: 'X' 'SAPMF05A' '1001',

' ' 'BDC_OKCODE' '=0006'.

CONCATENATE 'ACGL_ITEM-HKONT(0' IDX ')' INTO FNAM.

PERFORM POPULATE_BDCTABLE USING: ' ' FNAM T_DET-HKONT.

CONCATENATE 'ACGL_ITEM-SHKZG(0' IDX ')' INTO FNAM.

PERFORM POPULATE_BDCTABLE USING ' ' FNAM T_DET-SHKZG.

CONCATENATE 'ACGL_ITEM-WRBTR(0' IDX ')' INTO FNAM.

PERFORM POPULATE_BDCTABLE USING ' ' FNAM T_DET-WRBTR.

ENDIF.

ENDFORM. " BDC

Re: Table control to Excel

Former Member — Mon, 12 Nov 2007 11:34:02 GMT

Hi Eric,

I have mentioned the material number 025774 . Its in MARA / MARC with plant 1033. Do you have model code for excel download manually using OLE methods.

If then kindly send it asap.I am new for this area.

Regards,

K.Sudhakaran.

Re: Table control to Excel

JozsefSzikszai — Mon, 12 Nov 2007 13:14:27 GMT

hi Sudha,

do a search o the forum, there are plenty of examples...

for the text fomat you have to use the following statement:

SET PROPERTY OF <i>cell</i> 'NumberFormat' = '@'.

topic Re: Table control to Excel in Application Development and Automation Discussions

Table control to Excel

Re: Table control to Excel

Re: Table control to Excel

Re: Table control to Excel

Re: Table control to Excel

Re: Table control to Excel