topic Re: Excel function module in Application Development and Automation Discussions

Excel function module

Former Member — Fri, 16 Nov 2007 04:23:19 GMT

Hi all

can i know what is the function module to:

Downloads internal table and opens it in MS Excel

and

Uploads an *.xls file to internal table.

thks.

Re: Excel function module

Former Member — Fri, 16 Nov 2007 04:25:24 GMT

SAP_CONVERT_TO_XLS_FORMAT

TEXT_CONVERT_XLS_TO_SAP

Re: Excel function module

former_member189059 — Fri, 16 Nov 2007 04:31:23 GMT

<b>GUI_DOWNLOAD</b> and <b>GUI_UPLOAD</b>

Re: Excel function module

Former Member — Fri, 16 Nov 2007 04:31:31 GMT

GUI_UPLOAD

GUI_DOWNLOAD

Give file type as 'ASC'

Re: Excel function module

Former Member — Fri, 16 Nov 2007 04:33:38 GMT

For upload check

AT SELECTION-SCREEN ON VALUE-REQUEST FOR filename.

CALL FUNCTION 'KD_GET_FILENAME_ON_F4'

EXPORTING

mask = '*.xls'

static = 'X'

CHANGING

file_name = filename.

START-OF-SELECTION.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

filename = filename

i_begin_col = begcol

i_begin_row = begrow

i_end_col = endcol

i_end_row = endrow

TABLES

intern = intern

EXCEPTIONS

inconsistent_parameters = 1

upload_ole = 2

OTHERS = 3.

Praveen

Re: Excel function module

Former Member — Fri, 16 Nov 2007 04:44:49 GMT

Hi,

Please check the below sample program, whcich will help u in solving the problem.


*&---------------------------------------------------------------------*
*&      Form  DOWNLOAD_TO_EXCEL
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM DOWNLOAD_TO_EXCEL.

DATA: FNAME LIKE RLGRAP-FILENAME.
DATA: FILENAME LIKE IBIPPARMS-PATH.
DATA: BEGIN OF T_FIELDNAMES OCCURS 0,
        FNAME(50) TYPE C,
      END OF T_FIELDNAMES.

*Move fieldnames to interal table
MOVE SPACE  TO T_FIELDNAMES-FNAME.
APPEND T_FIELDNAMES.

MOVE C_PLNT  TO T_FIELDNAMES-FNAME.
APPEND T_FIELDNAMES.

MOVE C_CTRY  TO T_FIELDNAMES-FNAME.
APPEND T_FIELDNAMES.

MOVE C_MATERIAL TO T_FIELDNAMES-FNAME.
APPEND T_FIELDNAMES.

MOVE C_ABBREVIATION TO T_FIELDNAMES-FNAME.
APPEND T_FIELDNAMES.

MOVE C_COUNTIRFICATION_REQUIREMENT  TO T_FIELDNAMES-FNAME.
APPEND T_FIELDNAMES.

MOVE C_EXPIRY_DATE TO T_FIELDNAMES-FNAME.
APPEND T_FIELDNAMES.

MOVE C_CREATED TO T_FIELDNAMES-FNAME.
APPEND T_FIELDNAMES.

MOVE C_CREATED_BY  TO T_FIELDNAMES-FNAME.
APPEND T_FIELDNAMES.

MOVE C_ACTIVATED  TO T_FIELDNAMES-FNAME.
APPEND T_FIELDNAMES.

MOVE C_ACTIVATED_BY  TO T_FIELDNAMES-FNAME.
APPEND T_FIELDNAMES.

MOVE C_D  TO T_FIELDNAMES-FNAME.
APPEND T_FIELDNAMES.

*Import internal table with output data
IMPORT: I_DATA FROM MEMORY ID 'YLMC'.

CALL FUNCTION 'F4_FILENAME'
 EXPORTING
   PROGRAM_NAME        = SYST-CPROG
   DYNPRO_NUMBER       = SYST-DYNNR
   FIELD_NAME          = ' '
 IMPORTING
   FILE_NAME           = FILENAME
          .

FNAME = FILENAME.

* Call function to download data to excel
CALL FUNCTION 'MS_EXCEL_OLE_STANDARD_DAT'
  EXPORTING
    FILE_NAME                       = FNAME
*   CREATE_PIVOT                    = 0
*   DATA_SHEET_NAME                 = ' '
*   PIVOT_SHEET_NAME                = ' '
*   PASSWORD                        = ' '
*   PASSWORD_OPTION                 = 0
 TABLES
*   PIVOT_FIELD_TAB                 =
    DATA_TAB                        = I_DATA
    FIELDNAMES                      = T_FIELDNAMES
 EXCEPTIONS
   FILE_NOT_EXIST                  = 1
   FILENAME_EXPECTED               = 2
   COMMUNICATION_ERROR             = 3
   OLE_OBJECT_METHOD_ERROR         = 4
   OLE_OBJECT_PROPERTY_ERROR       = 5
   INVALID_FILENAME                = 6
   INVALID_PIVOT_FIELDS            = 7
   DOWNLOAD_PROBLEM                = 8
   OTHERS                          = 9
          .
IF SY-SUBRC <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
*         WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.

ENDFORM.                    " DOWNLOAD_TO_EXCEL

Re: Excel function module

Former Member — Fri, 16 Nov 2007 04:50:11 GMT

Hi Gary,

First for download

U use FM SAP_CONVERT_TO_XLS_FORMAT

to convert the int table into excel

Then use FM GUI_DOWNLOAD

2nd for upload

u can use FM

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

filename = file_name

i_begin_col = c_beg_col

i_begin_row = g_beg_row

i_end_col = c_end_col

i_end_row = g_end_row

TABLES

intern = t_excel_import

EXCEPTIONS

inconsistent_parameters = 1

upload_ole = 2

OTHERS = 3.

IF sy-subrc <> 0.

MESSAGE e003.

Error : Error while reading the file !!!

ENDIF.

IF NOT t_excel_import[] IS INITIAL.

LOOP AT t_excel_import.

CASE t_excel_import-col.

WHEN c_0001.

g_t_upload-matnr = t_excel_import-value.

WHEN c_0002.

lg_t_upload-maktx = t_excel_import-value.

WHEN c_0003.

g_t_upload-kwmeng = t_excel_import-value.

WHEN c_0004.

g_t_upload-meins = t_excel_import-value.

ENDCASE.

IF t_excel_import-col EQ c_0004.

APPEND g_t_upload.

CLEAR g_t_upload.

ENDIF.

ENDLOOP.

ENDIF.

Pls reward pts if it is helpfull

Regards

Srimanta

Re: Excel function module

Former Member — Fri, 16 Nov 2007 05:07:07 GMT

Hi Gary,

to upload an excel file into internal table use these function modules :

to get the name of excel file from system use :

DATA: FILENAME LIKE RLGRAP-FILENAME.

CALL FUNCTION 'KD_GET_FILENAME_ON_F4'

EXPORTING

PROGRAM_NAME = SYST-REPID

DYNPRO_NUMBER = SYST-DYNNR

FIELD_NAME =
STATIC = ' X'
MASK = '*.txt'

CHANGING

FILE_NAME = IFILE

EXCEPTIONS
MASK_TOO_LONG = 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.

To upload the data form excel file use this funcn module :

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

FILENAME = IFILE

I_BEGIN_COL = 1

I_BEGIN_ROW = 2

I_END_COL = 47

I_END_ROW = 65536

TABLES

INTERN = ITAB_UPLD

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.

***Refer to the parameters to know the type declaration of itab_upld.

*Sort table itab_upld by rows and columns.

SORT ITAB_UPLD[] BY ROW COL.

APPEND ITAB_TEXT.

LOOP AT ITAB_UPLD.

AT NEW ROW.

MOVE ITAB_UPLD-VALUE TO ITAB_TEXT.

V_ROW = V_ROW + 1.

V_COL = 1.

IF SY-TABIX NE 1.

APPEND ITAB_TEXT.

ENDIF.

ENDAT.

Re: Excel function module

Former Member — Fri, 16 Nov 2007 05:25:33 GMT

You can also refer to this link

Please reward points if my posts helps.