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: 

Upload data from excel with vba by calling a function module

Former Member
0 Kudos

Hello all,

i have a problem with the function module "ALSM_EXCEL_TO_INTERNAL_TABLE". I will call this function module with vba to load data from excel to sap with a Buttonclick. I have copied this function module and set it remotable. But i can´t call it from excel.

Can you give me some tips how can i

upload

data from excel with vba by click a button.

The problem seems the function: call method cl_gui_frontend_services=>clipboard_import in the function module, because when i comment this function call the vba-call is true but no results.

How can I call the function module correct with vba?

Thanks a lot for your tips!!!!

Chris

Message was edited by:

Christoph Kirschner

1 ACCEPTED SOLUTION

Former Member
0 Kudos

hi

good

Refer the below program to upload data to internal table from excel.

report ZBDC_MM01_NEW no standard page heading line-size 255.

DATA : BEGIN OF STR,

MATNR LIKE RMMG1-MATNR,

MBRSH LIKE RMMG1-MBRSH,

MTART LIKE RMMG1-MTART,

MAKTX LIKE MAKT-MAKTX,

MEINS LIKE MARA-MEINS,

END OF STR.

DATA : ITAB LIKE TABLE OF STR WITH HEADER LINE.

CALL FUNCTION 'UPLOAD'

EXPORTING

  • CODEPAGE = ' '

FILENAME = 'C:\MAT.TXT'

FILETYPE = 'DAT'

  • ITEM = ' '

  • FILEMASK_MASK = ' '

  • FILEMASK_TEXT = ' '

  • FILETYPE_NO_CHANGE = ' '

  • FILEMASK_ALL = ' '

  • FILETYPE_NO_SHOW = ' '

  • LINE_EXIT = ' '

  • USER_FORM = ' '

  • USER_PROG = ' '

  • SILENT = 'S'

  • IMPORTING

  • FILESIZE =

  • CANCEL =

  • ACT_FILENAME =

  • ACT_FILETYPE =

TABLES

data_tab = ITAB

  • EXCEPTIONS

  • CONVERSION_ERROR = 1

  • INVALID_TABLE_WIDTH = 2

  • INVALID_TYPE = 3

  • NO_BATCH = 4

  • UNKNOWN_ERROR = 5

  • GUI_REFUSE_FILETRANSFER = 6

  • OTHERS = 7

.

IF sy-subrc <> 0.

  • MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO

  • WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

ENDIF.

go through this link

http://sapass.metro.client.jp/Download/BdcProcessor.htm

reward point if helpful.

thanks

mrutyun^

4 REPLIES 4

Former Member
0 Kudos

hi

good

Refer the below program to upload data to internal table from excel.

report ZBDC_MM01_NEW no standard page heading line-size 255.

DATA : BEGIN OF STR,

MATNR LIKE RMMG1-MATNR,

MBRSH LIKE RMMG1-MBRSH,

MTART LIKE RMMG1-MTART,

MAKTX LIKE MAKT-MAKTX,

MEINS LIKE MARA-MEINS,

END OF STR.

DATA : ITAB LIKE TABLE OF STR WITH HEADER LINE.

CALL FUNCTION 'UPLOAD'

EXPORTING

  • CODEPAGE = ' '

FILENAME = 'C:\MAT.TXT'

FILETYPE = 'DAT'

  • ITEM = ' '

  • FILEMASK_MASK = ' '

  • FILEMASK_TEXT = ' '

  • FILETYPE_NO_CHANGE = ' '

  • FILEMASK_ALL = ' '

  • FILETYPE_NO_SHOW = ' '

  • LINE_EXIT = ' '

  • USER_FORM = ' '

  • USER_PROG = ' '

  • SILENT = 'S'

  • IMPORTING

  • FILESIZE =

  • CANCEL =

  • ACT_FILENAME =

  • ACT_FILETYPE =

TABLES

data_tab = ITAB

  • EXCEPTIONS

  • CONVERSION_ERROR = 1

  • INVALID_TABLE_WIDTH = 2

  • INVALID_TYPE = 3

  • NO_BATCH = 4

  • UNKNOWN_ERROR = 5

  • GUI_REFUSE_FILETRANSFER = 6

  • OTHERS = 7

.

IF sy-subrc <> 0.

  • MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO

  • WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

ENDIF.

go through this link

http://sapass.metro.client.jp/Download/BdcProcessor.htm

reward point if helpful.

thanks

mrutyun^

Former Member
0 Kudos

hi Christoph,

Use FM GUI_UPLOAD or AA_FILE_UPLOAD_EXCEL instead.

Hope it helps.

Lokesh

0 Kudos

Hi Lokesh,

thanks for your tips. I will tried it.

Christoph

0 Kudos

HI

Uploading data directly from Excel file format


*
* Upload data direct from excel.xls file to SAP
*
REPORT ZEXCELUPLOAD.

PARAMETERS: filename LIKE rlgrap-filename MEMORY ID M01,
            begcol TYPE i DEFAULT 1 NO-DISPLAY,
            begrow TYPE i DEFAULT 1 NO-DISPLAY,
            endcol TYPE i DEFAULT 100 NO-DISPLAY,
            endrow TYPE i DEFAULT 32000 NO-DISPLAY.
* Tick don't append header
PARAMETERS: kzheader AS CHECKBOX.

DATA: BEGIN OF intern OCCURS 0.
        INCLUDE STRUCTURE  alsmex_tabline.
DATA: END OF intern.

DATA: BEGIN OF intern1 OCCURS 0.
        INCLUDE STRUCTURE  alsmex_tabline.
DATA: END OF intern1.

DATA: BEGIN OF t_col OCCURS 0,
       col LIKE alsmex_tabline-col,
       size TYPE i.
DATA: END OF t_col.

DATA: zwlen TYPE i,
      zwlines TYPE i.

DATA: BEGIN OF fieldnames OCCURS 3,
        title(60),
        table(6),
        field(10),
        kz(1),
      END OF fieldnames.
* No of columns
DATA: BEGIN OF data_tab OCCURS 0,
       value_0001(50),
       value_0002(50),
       value_0003(50),
       value_0004(50),
       value_0005(50),
       value_0006(50),
       value_0007(50),
       value_0008(50),
       value_0009(50),
       value_0010(50),
       value_0011(50),
       value_0012(50),
       value_0013(50),
       value_0014(50),
       value_0015(50),
       value_0016(50),
       value_0017(50),
       value_0018(50),
       value_0019(50),
       value_0020(50),
       value_0021(50),
       value_0022(50),
       value_0023(50),
       value_0024(50),
       value_0025(50),
       value_0026(50),
       value_0027(50),
       value_0028(50),
       value_0029(50),
       value_0030(50),
       value_0031(50),
       value_0032(50),
       value_0033(50),
       value_0034(50),
       value_0035(50),
       value_0036(50),
       value_0037(50),
       value_0038(50),
       value_0039(50),
       value_0040(50),
       value_0041(50),
       value_0042(50),
       value_0043(50),
       value_0044(50),
       value_0045(50),
       value_0046(50),
       value_0047(50),
       value_0048(50),
       value_0049(50),
       value_0050(50),
       value_0051(50),
       value_0052(50),
       value_0053(50),
       value_0054(50),
       value_0055(50),
       value_0056(50),
       value_0057(50),
       value_0058(50),
       value_0059(50),
       value_0060(50),
       value_0061(50),
       value_0062(50),
       value_0063(50),
       value_0064(50),
       value_0065(50),
       value_0066(50),
       value_0067(50),
       value_0068(50),
       value_0069(50),
       value_0070(50),
       value_0071(50),
       value_0072(50),
       value_0073(50),
       value_0074(50),
       value_0075(50),
       value_0076(50),
       value_0077(50),
       value_0078(50),
       value_0079(50),
       value_0080(50),
       value_0081(50),
       value_0082(50),
       value_0083(50),
       value_0084(50),
       value_0085(50),
       value_0086(50),
       value_0087(50),
       value_0088(50),
       value_0089(50),
       value_0090(50),
       value_0091(50),
       value_0092(50),
       value_0093(50),
       value_0094(50),
       value_0095(50),
       value_0096(50),
       value_0097(50),
       value_0098(50),
       value_0099(50),
       value_0100(50).
DATA: END OF data_tab.
DATA: tind(4) TYPE n.
DATA: zwfeld(19).
FIELD-SYMBOLS: <fs1>.

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.

  IF sy-subrc <> 0.
    WRITE:/ 'Upload Error ', SY-SUBRC.
  ENDIF.

END-OF-SELECTION.

  LOOP AT intern.
    intern1 = intern.
    CLEAR intern1-row.
    APPEND intern1.
  ENDLOOP.

  SORT intern1 BY col.
  LOOP AT intern1.
    AT NEW col.
      t_col-col = intern1-col.
      APPEND t_col.
    ENDAT.
    zwlen = strlen( intern1-value ).
    READ TABLE t_col WITH KEY col = intern1-col.
    IF sy-subrc EQ 0.
      IF zwlen > t_col-size.
        t_col-size = zwlen.
*                          Internal Table, Current Row Index
        MODIFY t_col INDEX sy-tabix.
      ENDIF.
    ENDIF.
  ENDLOOP.

  DESCRIBE TABLE t_col LINES zwlines.

  SORT intern BY row col.
  IF kzheader = 'X'.
    LOOP AT intern.
      fieldnames-title = intern-value.
      APPEND fieldnames.
      AT END OF row.
        EXIT.
      ENDAT.
    ENDLOOP.
  ELSE.
    DO zwlines TIMES.
      WRITE sy-index TO fieldnames-title.
      APPEND fieldnames.
    ENDDO.
  ENDIF.

  SORT intern BY row col.
  LOOP AT intern.
    IF kzheader = 'X'
    AND intern-row = 1.
      CONTINUE.
    ENDIF.
    tind = intern-col.
    CONCATENATE 'DATA_TAB-VALUE_' tind INTO zwfeld.
    ASSIGN (zwfeld) TO <fs1>.
    <fs1> = intern-value.
    AT END OF row.
      APPEND data_tab.
      CLEAR data_tab.
    ENDAT.
  ENDLOOP.

  CALL FUNCTION 'DISPLAY_BASIC_LIST'
       EXPORTING
            file_name     = filename
       TABLES
            data_tab      = data_tab
            fieldname_tab = fieldnames.

*-- End of Program 

<b>Excel Upload Alternative - KCD_EXCEL_OLE_TO_INT_CONVERT</b>

*Title : Excel Uploading
***************************************************************
TYPES:   BEGIN OF t_datatab,
         col1(25)  TYPE c,
         col2(30)  TYPE c,
         col3(30)  TYPE c,
         col4(30)  TYPE c,
         col5(30)  TYPE c,
         col6(30)  TYPE c,
         col7(30) TYPE c,
         col8(30)  TYPE c,
         col9(30)  TYPE c,
         col10(30)  TYPE c,
         col11(30)    TYPE c,
       END OF t_datatab.
DATA: it_datatab TYPE STANDARD TABLE OF t_datatab INITIAL SIZE 0,
      wa_datatab TYPE t_datatab.
Data : p_table type t_datatab occurs 0 with header line.
DATA : gd_scol   TYPE i VALUE '1',
       gd_srow   TYPE i VALUE '1',
       gd_ecol   TYPE i VALUE '256',
       gd_erow   TYPE i VALUE '65536'.

DATA: it_tab TYPE filetable,
      gd_subrc TYPE i.
field-symbols : <fs>.

*********************************************************************
*Selection screen definition
SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE text-001.
PARAMETERS:  p_file LIKE rlgrap-filename
               DEFAULT 'c:test.xls' OBLIGATORY.   " File Name
SELECTION-SCREEN END OF BLOCK b1.


***********************************************************************
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
  REFRESH: it_tab.
  CALL METHOD cl_gui_frontend_services=>file_open_dialog
    EXPORTING
      window_title     = 'Select File'
      default_filename = '*.xls'
      multiselection   = ' '
    CHANGING
      file_table       = it_tab
      rc               = gd_subrc.


************************************************************************

  LOOP AT it_tab INTO p_file.
*    so_fpath-sign = 'I'.
*    so_fpath-option = 'EQ'.
*    append so_fpath.
  ENDLOOP.


***********************************************************************
START-OF-SELECTION.
  PERFORM upload_excel_file TABLES   it_datatab
                             USING   p_file
                                     gd_scol
                                     gd_srow
                                     gd_ecol
                                     gd_erow.


***********************************************************************
* END-OF-SELECTION.
END-OF-SELECTION.
  LOOP AT it_datatab INTO wa_datatab.
    WRITE:/ wa_datatab-col1,
            wa_datatab-col2,
            wa_datatab-col3,
            wa_datatab-col4,
            wa_datatab-col5,
            wa_datatab-col6,
            wa_datatab-col7,
            wa_datatab-col8,
            wa_datatab-col9,
            wa_datatab-col10,
            wa_datatab-col11.
  ENDLOOP.


*&--------------------------------------------------------------------*
*&      Form  UPLOAD_EXCEL_FILE
*&--------------------------------------------------------------------*
*       upload excel spreadsheet into internal table
*---------------------------------------------------------------------*
*      -->P_TABLE    Table to return excel data into
*      -->P_FILE     file name and path
*      -->P_SCOL     start column
*      -->P_SROW     start row
*      -->P_ECOL     end column
*      -->P_EROW     end row
*---------------------------------------------------------------------*
FORM upload_excel_file TABLES   p_table
                       USING    p_file
                                p_scol
                                p_srow
                                p_ecol
                                p_erow.

  DATA : lt_intern TYPE  kcde_cells OCCURS 0 WITH HEADER LINE.
* Has the following format:
*             Row number   | Colum Number   |   Value
*             ---------------------------------------
*      i.e.     1                 1             Name1
*               2                 1             Joe

  DATA : ld_index TYPE i.


* Note: Alternative function module - 'ALSM_EXCEL_TO_INTERNAL_TABLE'
  CALL FUNCTION 'KCD_EXCEL_OLE_TO_INT_CONVERT'
    EXPORTING
      filename                = p_file
      i_begin_col             = p_scol
      i_begin_row             = p_srow
      i_end_col               = p_ecol
      i_end_row               = p_erow
    TABLES
      intern                  = LT_INTERN
    EXCEPTIONS
      inconsistent_parameters = 1
      upload_ole              = 2
      OTHERS                  = 3.
  IF sy-subrc <> 0.
    FORMAT COLOR COL_BACKGROUND INTENSIFIED.
    WRITE:/ 'Error Uploading file'.
    EXIT.
  ENDIF.

  IF lt_intern[] IS INITIAL.
    FORMAT COLOR COL_BACKGROUND INTENSIFIED.
    WRITE:/ 'No Data Uploaded'.
    EXIT.
  ELSE.
    SORT lt_intern BY row col.
    LOOP AT lt_intern.
     MOVE lt_intern-col TO ld_index.
     assign component ld_index of structure
     p_table to <fs>.
 move : lt_intern-value to <fs>.
*     MOVE lt_intern-value TO p_table.
      AT END OF row.
        APPEND p_table.
        CLEAR p_table.
      ENDAT.
    ENDLOOP.
  ENDIF.
ENDFORM.                    "UPLOAD_EXCEL_FILE

Regards

Pavan