topic Re: Dropdown values are missing while using the function module KCD_EXCEL_OLE_TO_INT_CONVERT in Application Development and Automation Discussions

Dropdown values are missing while using the function module KCD_EXCEL_OLE_TO_INT_CONVERT

Former Member — Wed, 26 Feb 2014 08:40:10 GMT

Hi all,

when i am trying to upload the excel template by using the function module " KCD_EXCEL_OLE_TO_INT_CONVERT" to an internal table some field values are missing.

i am using the function module " KCD_EXCEL_OLE_TO_INT_CONVERT" and i am uploading an excel template and downloading that excel data into an internal table but some field values are missing while uploading the excel template and i found that the field which contains the drop down list values those values are missing.

And also when i am uploading the excel template by using the function module" KCD_EXCEL_OLE_TO_INT_CONVERT" when i am coming out of this function module one pop up is coming. how can i avoid this popup, can any one please provide me the solution thanks in advance.

FYI...it is showing the below popup screen ..

Thanks,

Koushik

Re: Dropdown values are missing while using the function module KCD_EXCEL_OLE_TO_INT_CONVERT

former_member192023 — Wed, 26 Feb 2014 09:11:07 GMT

Hi Bharath,

I can use it in my system.

May I see you source logic?

Regards

Re: Dropdown values are missing while using the function module KCD_EXCEL_OLE_TO_INT_CONVERT

Former Member — Wed, 26 Feb 2014 09:45:51 GMT

hi Feiyun,

below is the code(highlighted in bold)and after coming out of the function module i am getting popup and also the drop down values are missing.

DATA: ls_control         TYPE zsfi_fiupload_control,
         ls_header          TYPE zsfi_fiupload_header_v13,
         ls_item            TYPE zsfi_fiupload_item_v13,
         ls_trailer         TYPE zsfi_fiupload_control,
         l_line_count       LIKE zsfi_fiupload_control-rec_count,
         l_current_curr     TYPE kcde_cells-value,
         l_current_postdate TYPE kcde_cells-value,
         l_current_compcode TYPE kcde_cells-value,
         l_currentrow       TYPE kcde_cells-row,
         l_filename         LIKE rlgrap-filename.

   DATA: BEGIN OF lt_intern OCCURS 0.
           INCLUDE STRUCTURE kcde_cells.
   DATA: END OF lt_intern.

   DATA: BEGIN OF lt_items OCCURS 0,
           a TYPE kcde_cells-value,
           b TYPE kcde_cells-value,
           c TYPE kcde_cells-value,
           d TYPE kcde_cells-value,
           e TYPE kcde_cells-value,
           f TYPE kcde_cells-value,
           g TYPE kcde_cells-value,
           h TYPE kcde_cells-value,
           i TYPE kcde_cells-value,
           j TYPE kcde_cells-value,
           k TYPE kcde_cells-value,
           l TYPE kcde_cells-value,
           m TYPE kcde_cells-value,
           n TYPE kcde_cells-value,
           o TYPE kcde_cells-value,
           p TYPE kcde_cells-value,
           q TYPE kcde_cells-value,
           r TYPE kcde_cells-value,
           s TYPE kcde_cells-value,
           t TYPE kcde_cells-value,
           u TYPE kcde_cells-value,
           v TYPE kcde_cells-value,
           w TYPE kcde_cells-value,
           x TYPE kcde_cells-value,
           y TYPE kcde_cells-value,
           z TYPE kcde_cells-value,
           aa TYPE kcde_cells-value,
           ab TYPE kcde_cells-value,
           ac TYPE kcde_cells-value,
           ad TYPE kcde_cells-value,
           ae TYPE kcde_cells-value,
           af TYPE kcde_cells-value,
           ag TYPE kcde_cells-value,
           ah TYPE kcde_cells-value,
           ai TYPE kcde_cells-value,
           AJ TYPE KCDE_CELLS-VALUE,
           AK TYPE KCDE_CELLS-VALUE,
           END OF lt_items.

   l_filename = p_flpath.

* the file must be an xls file, not xlsx or comma seperated
   IF p_flpath NS '.xls' AND p_flpath NS '.XLS'.
     MESSAGE i261(zfi).
     g_error = 'X'.
     EXIT.
   ENDIF.

* upload excel file to a generic table
   CALL FUNCTION 'KCD_EXCEL_OLE_TO_INT_CONVERT'
     EXPORTING
       filename                = l_filename
       i_begin_col             = 1
       i_begin_row             = 1
       i_end_col               = 150
       i_end_row               = 65000
     TABLES
       intern                  = lt_intern
     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.

* validate that the format is in the restatement format
* this is the only template to have the ledger group in cell G-2
** READ TABLE lt_intern WITH KEY row = '0002' col = '0007'.
** IF sy-subrc <> 0 OR lt_intern-value IS INITIAL.
**    MESSAGE i260(zfi).
**    g_error = 'X'.
**    EXIT.
** ENDIF.

* create control record values
   ls_control-rec_type = 'C'.
   ls_control-global_id = 'GLU-03000'.
   ls_control-local_id = ''.
   ls_control-rec_count = ''.
   CONCATENATE sy-datum sy-uzeit INTO ls_control-date.
   ls_control-version = '12'.
   ls_control-email = ''.
   ls_control-eor = 'X'.

   ls_trailer = ls_control.

* open output file and add the control record
   OPEN DATASET p_unixfilename FOR OUTPUT IN TEXT MODE ENCODING DEFAULT.
   TRANSFER ls_control TO p_unixfilename.

* create header record values, will be transfered to file in the item loop
   ls_header-rec_type = 'H'.
   READ TABLE lt_intern WITH KEY row = '0017' col = '0003'.
   IF sy-subrc = 0.
     ls_header-header_txt = lt_intern-value.
   ENDIF.

   READ TABLE lt_intern WITH key row = '0020' col = '0002'.
   IF sy-subrc = 0.
     ls_header-comp_code = lt_intern-value.
   ENDIF.
************doc date
   READ TABLE lt_intern WITH KEY row = '0011' col = '0003'.
   IF sy-subrc = 0.
     ls_header-pstng_date = lt_intern-value.
   ENDIF.
************posting date
     ls_header-doc_date = sy-datum.

   READ TABLE lt_intern WITH KEY row = '0009' col = '0003'.
   IF sy-subrc = 0.
     ls_header-doc_type = lt_intern-value.
   ENDIF.

   READ TABLE lt_intern WITH KEY row = '0014' col = '0003'.
   IF sy-subrc = 0.
     ls_header-ref_doc_no = lt_intern-value.
   ENDIF.
************currency moved to line items
     READ TABLE lt_intern WITH KEY row = '0020' col = '0001'.
   IF sy-subrc = 0.
   ls_header-currency = lt_intern-value.
   ENDIF.
   ls_header-trans_date = ''.

   READ TABLE lt_intern WITH KEY row = '0010' col = '0003'.
   IF sy-subrc = 0.
     ls_header-period = lt_intern-value.
   ENDIF.

   READ TABLE lt_intern WITH KEY row = '0006' col = '0003'.
   IF sy-subrc = 0.
     ls_header-reason_rev = lt_intern-value.
   ENDIF.

   READ TABLE lt_intern WITH KEY row = '0007' col = '0003'.
   IF sy-subrc = 0.
     ls_header-rev_date = lt_intern-value.
   ENDIF.

* READ TABLE lt_intern WITH KEY row = '0002' col = '0007'.
* IF sy-subrc = 0.
     ls_header-ledger_grp = ''.

* ENDIF.

* READ TABLE lt_intern WITH KEY row = '0013' col = '0013'.
* IF sy-subrc = 0.
     ls_header-exch_rate = ''.
* ENDIF.

   READ TABLE lt_intern WITH KEY row = '0008' col = '0003'.
   IF sy-subrc = 0.
     ls_header-calc_tax = lt_intern-value.
   ENDIF.

   ls_header-eor = 'X'.

* loop at the line items and append them to a table with multiple columns
   LOOP AT lt_intern WHERE row GE 20.

     IF l_currentrow <> lt_intern-row AND l_currentrow IS NOT INITIAL.
       APPEND lt_items.
       CLEAR lt_items.
     ENDIF.

     l_currentrow = lt_intern-row.

     CASE lt_intern-col.
       WHEN '0001'. lt_items-a = lt_intern-value.
       WHEN '0002'. lt_items-b = lt_intern-value.
       WHEN '0003'. lt_items-c = lt_intern-value.
       WHEN '0004'. lt_items-d = lt_intern-value.
       WHEN '0005'. lt_items-e = lt_intern-value.
       WHEN '0006'. lt_items-f = lt_intern-value.
       WHEN '0007'. lt_items-g = lt_intern-value.
       WHEN '0008'. lt_items-h = lt_intern-value.
       WHEN '0009'. lt_items-i = lt_intern-value.
       WHEN '0010'. lt_items-j = lt_intern-value.
       WHEN '0011'. lt_items-k = lt_intern-value.
       WHEN '0012'. lt_items-l = lt_intern-value.
       WHEN '0013'. lt_items-m = lt_intern-value.
       WHEN '0014'. lt_items-n = lt_intern-value.
       WHEN '0015'. lt_items-o = lt_intern-value.
       WHEN '0016'. lt_items-p = lt_intern-value.
       WHEN '0017'. lt_items-q = lt_intern-value.
       WHEN '0018'. lt_items-r = lt_intern-value.
       WHEN '0019'. lt_items-s = lt_intern-value.
       WHEN '0020'. lt_items-t = lt_intern-value.
       WHEN '0021'. lt_items-u = lt_intern-value.
       WHEN '0022'. lt_items-v = lt_intern-value.
       WHEN '0023'. lt_items-w = lt_intern-value.
       WHEN '0024'. lt_items-x = lt_intern-value.
       WHEN '0025'. lt_items-y = lt_intern-value.
       WHEN '0026'. lt_items-z = lt_intern-value.
       WHEN '0027'. lt_items-aa = lt_intern-value.
       WHEN '0028'. lt_items-ab = lt_intern-value.
       WHEN '0029'. lt_items-ac = lt_intern-value.
       WHEN '0030'. lt_items-ad = lt_intern-value.
       WHEN '0031'. lt_items-ae = lt_intern-value.
       WHEN '0032'. lt_items-af = lt_intern-value.
       WHEN '0033'. lt_items-ag = lt_intern-value.
       WHEN '0034'. lt_items-ah = lt_intern-value.
       WHEN '0035'. lt_items-ai = lt_intern-value.
       when '0036'. lt_items-aj = lt_intern-value.
       when '0037'. lt_items-ak = lt_intern-value.
     ENDCASE.

   ENDLOOP.

   APPEND lt_items.

* sort rows by the post date, comp code, curr.
* SORT lt_items BY ah a ai.

* loop at the items, creating a header record if the comp code, curr. or post date changes
   LOOP AT lt_items.

     ls_item-rec_type = 'I'.
     ls_item-post_key = lt_items-c.   "lt_items-b.
     IF lt_items-c GE '20' AND lt_items-c LE '39'.
       ls_item-vendor_no = lt_items-d.
     ELSEIF lt_items-c GE '0' AND lt_items-c LE '19'.
       ls_item-customer = lt_items-d.
     ELSEIF lt_items-c EQ '70' AND lt_items-c EQ '75'.
       ls_item-asset = lt_items-d.
     ELSE.
       ls_item-gl_account = lt_items-d.
     ENDIF.
     ls_item-asset_type = lt_items-m. "lt_items-l.
     ls_item-trans_type = lt_items-n. "lt_items-m.
     ls_item-print_flag = ''.
     ls_item-item_text = lt_items-j. "lt_items-i.
     ls_item-comp_code = lt_items-b. "lt_items-a.
     ls_item-bus_area = ''.
     ls_item-alloc_nmbr = lt_items-l. "lt_items-k.
     ls_item-tax_code = lt_items-q. "lt_items-p.
     ls_item-taxjurcode = lt_items-r. "lt_items-q.
     ls_item-profit_ctr = lt_items-g. "lt_items-f.
     ls_item-costcenter = lt_items-h. "lt_items-g.
     ls_item-orderid = lt_items-ac. "lt_items-ab.
     ls_item-wbs = lt_items-i. "lt_items-h.
     ls_item-trade_id = lt_items-o. "lt_items-n.
     ls_item-part_prctr = lt_items-p. "lt_items-o.
     ls_item-material = lt_items-t. "lt_items-s.
     ls_item-salesorg = lt_items-aa. "lt_items-z.
     ls_item-distr_chan = lt_items-ab. "lt_items-aa.
     ls_item-pmnttrms = ''.
     ls_item-pmtmthd = ''.
     ls_item-pmtmthsupl = ''.
     ls_item-alt_payee = ''.
     REPLACE ALL OCCURRENCES OF '(' IN lt_items-e WITH ''.
     REPLACE ALL OCCURRENCES OF ')' IN lt_items-e WITH ''.
     ls_item-amt_doccur = lt_items-e. "lt_items-d
     REPLACE ALL OCCURRENCES OF '(' IN lt_items-f WITH ''.
     REPLACE ALL OCCURRENCES OF ')' IN lt_items-f WITH ''.
     ls_item-loc_amt = lt_items-f. "lt_items-e
     ls_item-loc = lt_items-k. "lt_items-j.
     ls_item-sas_revtype = lt_items-s. "lt_items-r.
     ls_item-rev_type = lt_items-u. "lt_items-t.
     ls_item-rev_chnl = lt_items-v. "lt_items-u.
     ls_item-bill_to = lt_items-x. "lt_items-w.
     ls_item-payer = lt_items-z. "lt_items-y.
     ls_item-ship_to = lt_items-y.
     ls_item-tax_amnt = lt_items-x.
     ls_item-func_area = ''.
     ls_item-expend_date = lt_items-ae. "lt_items-ad.
     ls_item-wtax_code = lt_items-af. "lt_items-ae.
     ls_item-wtax_base = lt_items-ag. "lt_items-af.
     ls_item-wtax_amount = lt_items-ah. "lt_items-ag.
     ls_item-payment_block = ''.
     ls_item-grp_amt = ''.
     ls_item-anc_amt = ''.
     ls_item-permit_payee = ''.
     ls_item-value_date = ''.
     ls_item-pernr   = ''.
     ls_item-legacy1 = ''.
     ls_item-legacy2 = ''.
     ls_item-legacy3 = ''.
     ls_item-legacy4 = ''.
     ls_item-legacy5 = ''.
     ls_item-legacy6 = ''.
     ls_item-legacy7 = ''.
     ls_item-legacy8 = ''.
     ls_item-legacy9 = ''.
     ls_item-legacy10 = ''.
     ls_item-eor = 'X'.

     TRANSLATE lt_items-ai TO UPPER CASE.
     TRANSLATE lt_items-b TO UPPER CASE.
     TRANSLATE lt_items-a TO UPPER CASE.
     TRANSLATE lt_items-aj TO UPPER CASE.

     IF l_current_curr <> lt_items-a "lt_items-ai
       OR l_current_postdate <> ls_header-pstng_date "lt_items-aj
       OR l_current_compcode <> lt_items-b.

       l_current_curr = lt_items-a.
       l_current_postdate = ls_header-pstng_date. "lt_items-aj.
       l_current_compcode = lt_items-b.

       ls_header-comp_code = l_current_compcode.
       ls_header-currency = l_current_curr.
       ls_header-pstng_date = l_current_postdate.
       ls_header-doc_date = l_current_postdate.

       TRANSFER ls_header TO p_unixfilename.
       l_line_count = l_line_count + 1.
     ENDIF.

     TRANSFER ls_item TO p_unixfilename.
     l_line_count = l_line_count + 1.
   ENDLOOP.

   ls_trailer-rec_type = 'T'.
   ls_trailer-rec_count = l_line_count.
   TRANSFER ls_trailer TO p_unixfilename.
   CLOSE DATASET p_unixfilename.

* change the format to fixed format for the remainder of the load program
   p_fixed = 'X'.
   p_rstmnt = ''.

Thanks,

Koushik

Re: Dropdown values are missing while using the function module KCD_EXCEL_OLE_TO_INT_CONVERT

former_member192023 — Wed, 26 Feb 2014 09:53:25 GMT

Hi Bharath,

Try to change below parameter value of the function:

i_end_col = 150-->256

i_end_row = 65000->65536

P_ECOL	256
P_EROW	65536

Re: Dropdown values are missing while using the function module KCD_EXCEL_OLE_TO_INT_CONVERT

Former Member — Wed, 26 Feb 2014 10:03:30 GMT

Hi Feiyun,

I changed the two parameters even though i am getting the pop up screen.

thanks,

koushik

Re: Dropdown values are missing while using the function module KCD_EXCEL_OLE_TO_INT_CONVERT

hiriyappa_myageri — Wed, 26 Feb 2014 11:18:40 GMT

Hi,

In some function modules we can restrict the pop ups and display output list and all by passing x to the export parameters .

but in this function module we do not have such option ,its standards functionality present in that function module.

Regards,

Hiriyappa

Re: Dropdown values are missing while using the function module KCD_EXCEL_OLE_TO_INT_CONVERT

Former Member — Wed, 26 Feb 2014 11:57:50 GMT

Hi Myageri,

i searched in google and i found that we can disable the pop ups by changing the settings in the macros in the excel template but i didn't get the full information,

is it possible that we can change the settings and disable the pop ups?

if it is possible how can we change the settings?

thanks,

koushik

Re: Dropdown values are missing while using the function module KCD_EXCEL_OLE_TO_INT_CONVERT

ThangaPrakash — Wed, 26 Feb 2014 12:17:03 GMT

Hello Bharath,

You can find lot of options in google to disable that pop up message in Excel sheet.

Please refer to the below link and try the same.

How to Avoid "Save Changes?" When You Close a Workbook

Kindly let me know if it works.

Regards,

Re: Dropdown values are missing while using the function module KCD_EXCEL_OLE_TO_INT_CONVERT

Former Member — Wed, 26 Feb 2014 12:40:23 GMT

Hi Prakash,

thanks for the reply i think it will solve my issue i guess i need to take approval and i need to discuss with my functional to change the macros mean while i want to discuss regarding the another issue the drop down values are missing is there any solution to read those values.

KCD_EXCEL_OLE_TO_INT_CONVERT function module is not reading the drop down values.

thanks in advance,

koushik

Re: Dropdown values are missing while using the function module KCD_EXCEL_OLE_TO_INT_CONVERT

former_member226419 — Wed, 26 Feb 2014 12:57:29 GMT

Hi,

Also I have tried one thing removed the headings from excel . I did one thing I have given column names in excel and then execute it doesn't shown any pop up but i directly gave values in the column in excel it shows me the pop up.

Please try this and let me know if this will solve or not.

Sumeet

Re: Dropdown values are missing while using the function module KCD_EXCEL_OLE_TO_INT_CONVERT

former_member226419 — Wed, 26 Feb 2014 13:18:34 GMT

Hi,

And also can you please try the below code.

TYPES: BEGIN OF t_datatab ,

col1(25) TYPE c,

col2(30) TYPE c,

col3(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 : 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.

*Selection screen definition

SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE text-001.

PARAMETERS: p_file LIKE rlgrap-filename

DEFAULT 'C:\Users\Dell\Desktop\test.xlsx' OBLIGATORY. " File Name

SELECTION-SCREEN END OF BLOCK b1.

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

* AT SELECTION-SCREEN

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.

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.

FIELD-SYMBOLS : <fs>.

* 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>.

AT END OF row.

APPEND p_table.

CLEAR p_table.

ENDAT.

ENDLOOP.

ENDIF.

ENDFORM. "UPLOAD_EXCEL_FILE

Sumeet

Re: Dropdown values are missing while using the function module KCD_EXCEL_OLE_TO_INT_CONVERT

Former Member — Wed, 26 Feb 2014 13:19:44 GMT

Hi Sumeet,

thanks for the reply but if i change the excel template i mean if i remove the headings it will become very complex in my scenario, because i have to make changes in so many places i think it will not works in my scenario.

Anyway thanks for the reply

Regards,

koushik