Community
Application Development and Automation 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
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Read only

ALSM_EXCEL_TO_INTERNAL_TABLE With Multiple sheets

Former Member

‎2010 Sep 21 11:21 AM

0 Likes
1,871

I copied FM ALSM_EXCEL_TO_INTERNAL_TABLE and created a Z FM and changed it to get Excel worksheet as a input Parameter. My problem is when I included my coding part (which marked with '###' sign) EXCEL.EXE in the windows task manger won't get removed.

So please check below coding and help me to solve this issue.

Coding is as follows:

FUNCTION zalsm_excel_to_internal_table .

*"----

-

""Local Interface:

*" IMPORTING

*" VALUE(FILENAME) LIKE RLGRAP-FILENAME

*" VALUE(I_BEGIN_COL) TYPE I

*" VALUE(I_BEGIN_ROW) TYPE I

*" VALUE(I_END_COL) TYPE I

*" VALUE(I_END_ROW) TYPE I

*" VALUE(I_WORKSHEET) TYPE STRING OPTIONAL

*" TABLES

*" INTERN STRUCTURE ZALSMEX_TABLINE

*" EXCEPTIONS

*" INCONSISTENT_PARAMETERS

*" UPLOAD_OLE

*"----

-

DATA: excel_tab TYPE ty_t_sender.

DATA: ld_separator TYPE c.

DATA: application TYPE ole2_object,

workbook TYPE ole2_object,

range TYPE ole2_object,

worksheet TYPE ole2_object.

DATA: h_cell TYPE ole2_object,

h_cell1 TYPE ole2_object.

DATA:

ld_rc TYPE i.

  • Rückgabewert der Methode "clipboard_export "

  • Makro für Fehlerbehandlung der Methods

DEFINE m_message.

case sy-subrc.

when 0.

when 1.

message id sy-msgid type sy-msgty number sy-msgno

with sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.

when others.

raise upload_ole.

endcase.

END-OF-DEFINITION.

  • check parameters

IF i_begin_row > i_end_row. RAISE inconsistent_parameters. ENDIF.

IF i_begin_col > i_end_col. RAISE inconsistent_parameters. ENDIF.

  • Get TAB-sign for separation of fields

CLASS cl_abap_char_utilities DEFINITION LOAD.

ld_separator = cl_abap_char_utilities=>horizontal_tab.

  • open file in Excel

IF application-header = space OR application-handle = -1.

CREATE OBJECT application 'Excel.Application'.

m_message.

ENDIF.

CALL METHOD OF application 'Workbooks' = workbook.

m_message.

CALL METHOD OF workbook 'Open' EXPORTING #1 = filename.

m_message.

*{+############To pass excel worksheet#################

*If you comment this part of the coding EXCEL.EXE in windows task manager will close correctly

IF i_worksheet <> ''. "This i_worksheet is the input parameter (String)

CALL METHOD OF application 'Worksheets' = worksheet

EXPORTING

#1 = i_worksheet.

m_message.

CALL METHOD OF worksheet 'Activate'.

m_message.

ENDIF.

*}+################:END################################

  • set property of application 'Visible' = 1.

  • m_message.

GET PROPERTY OF application 'ACTIVESHEET' = worksheet.

m_message.

  • mark whole spread sheet

CALL METHOD OF worksheet 'Cells' = h_cell

EXPORTING #1 = i_begin_row #2 = i_begin_col.

m_message.

CALL METHOD OF worksheet 'Cells' = h_cell1

EXPORTING #1 = i_end_row #2 = i_end_col.

m_message.

CALL METHOD OF worksheet 'RANGE' = range

EXPORTING #1 = h_cell #2 = h_cell1.

m_message.

CALL METHOD OF range 'SELECT'.

m_message.

  • copy marked area (whole spread sheet) into Clippboard

CALL METHOD OF range 'COPY'.

m_message.

  • read clipboard into ABAP

CALL METHOD cl_gui_frontend_services=>clipboard_import

IMPORTING

data = excel_tab

EXCEPTIONS

cntl_error = 1

  • ERROR_NO_GUI = 2

  • NOT_SUPPORTED_BY_GUI = 3

OTHERS = 4

.

IF sy-subrc <> 0.

MESSAGE a037(alsmex).

ENDIF.

PERFORM separated_to_intern_convert TABLES excel_tab intern

USING ld_separator.

  • clear clipboard

REFRESH excel_tab.

CALL METHOD cl_gui_frontend_services=>clipboard_export

IMPORTING

data = excel_tab

CHANGING

rc = ld_rc

EXCEPTIONS

cntl_error = 1

  • ERROR_NO_GUI = 2

  • NOT_SUPPORTED_BY_GUI = 3

OTHERS = 4

.

    • quit Excel and free ABAP Object - unfortunately, this does not kill

    • the Excel process

CALL METHOD OF application 'QUIT'.

m_message.

  • >>>>> Begin of change note 575877

  • to kill the Excel process it's necessary to free all used objects

FREE OBJECT h_cell. m_message.

FREE OBJECT h_cell1. m_message.

FREE OBJECT range. m_message.

FREE OBJECT worksheet. m_message.

FREE OBJECT workbook. m_message.

FREE OBJECT application. m_message.

  • <<<<< End of change note 575877

ENDFUNCTION.

I copied FM ALSM_EXCEL_TO_INTERNAL_TABLE and created a Z FM and changed it to get Excel worksheet as a input Parameter. My problem is when I included my coding part (which marked with '###' sign) EXCEL.EXE in the windows task manger won't get removed.

So please check below coding and help me to solve this issue.

Coding is as follows:

FUNCTION zalsm_excel_to_internal_table .

*"----

-

""Local Interface:

*" IMPORTING

*" VALUE(FILENAME) LIKE RLGRAP-FILENAME

*" VALUE(I_BEGIN_COL) TYPE I

*" VALUE(I_BEGIN_ROW) TYPE I

*" VALUE(I_END_COL) TYPE I

*" VALUE(I_END_ROW) TYPE I

*" VALUE(I_WORKSHEET) TYPE STRING OPTIONAL

*" TABLES

*" INTERN STRUCTURE ZALSMEX_TABLINE

*" EXCEPTIONS

*" INCONSISTENT_PARAMETERS

*" UPLOAD_OLE

*"----

-

DATA: excel_tab TYPE ty_t_sender.

DATA: ld_separator TYPE c.

DATA: application TYPE ole2_object,

workbook TYPE ole2_object,

range TYPE ole2_object,

worksheet TYPE ole2_object.

DATA: h_cell TYPE ole2_object,

h_cell1 TYPE ole2_object.

DATA:

ld_rc TYPE i.

  • Rückgabewert der Methode "clipboard_export "

  • Makro für Fehlerbehandlung der Methods

DEFINE m_message.

case sy-subrc.

when 0.

when 1.

message id sy-msgid type sy-msgty number sy-msgno

with sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.

when others.

raise upload_ole.

endcase.

END-OF-DEFINITION.

  • check parameters

IF i_begin_row > i_end_row. RAISE inconsistent_parameters. ENDIF.

IF i_begin_col > i_end_col. RAISE inconsistent_parameters. ENDIF.

  • Get TAB-sign for separation of fields

CLASS cl_abap_char_utilities DEFINITION LOAD.

ld_separator = cl_abap_char_utilities=>horizontal_tab.

  • open file in Excel

IF application-header = space OR application-handle = -1.

CREATE OBJECT application 'Excel.Application'.

m_message.

ENDIF.

CALL METHOD OF application 'Workbooks' = workbook.

m_message.

CALL METHOD OF workbook 'Open' EXPORTING #1 = filename.

m_message.

*{+############To pass excel worksheet#################

*If you comment this part of the coding EXCEL.EXE in windows task manager will close correctly

IF i_worksheet <> ''. "This i_worksheet is the input parameter (String)

CALL METHOD OF application 'Worksheets' = worksheet

EXPORTING

#1 = i_worksheet.

m_message.

CALL METHOD OF worksheet 'Activate'.

m_message.

ENDIF.

*}+################:END################################

  • set property of application 'Visible' = 1.

  • m_message.

GET PROPERTY OF application 'ACTIVESHEET' = worksheet.

m_message.

  • mark whole spread sheet

CALL METHOD OF worksheet 'Cells' = h_cell

EXPORTING #1 = i_begin_row #2 = i_begin_col.

m_message.

CALL METHOD OF worksheet 'Cells' = h_cell1

EXPORTING #1 = i_end_row #2 = i_end_col.

m_message.

CALL METHOD OF worksheet 'RANGE' = range

EXPORTING #1 = h_cell #2 = h_cell1.

m_message.

CALL METHOD OF range 'SELECT'.

m_message.

  • copy marked area (whole spread sheet) into Clippboard

CALL METHOD OF range 'COPY'.

m_message.

  • read clipboard into ABAP

CALL METHOD cl_gui_frontend_services=>clipboard_import

IMPORTING

data = excel_tab

EXCEPTIONS

cntl_error = 1

  • ERROR_NO_GUI = 2

  • NOT_SUPPORTED_BY_GUI = 3

OTHERS = 4

.

IF sy-subrc <> 0.

MESSAGE a037(alsmex).

ENDIF.

PERFORM separated_to_intern_convert TABLES excel_tab intern

USING ld_separator.

  • clear clipboard

REFRESH excel_tab.

CALL METHOD cl_gui_frontend_services=>clipboard_export

IMPORTING

data = excel_tab

CHANGING

rc = ld_rc

EXCEPTIONS

cntl_error = 1

  • ERROR_NO_GUI = 2

  • NOT_SUPPORTED_BY_GUI = 3

OTHERS = 4

.

    • quit Excel and free ABAP Object - unfortunately, this does not kill

    • the Excel process

CALL METHOD OF application 'QUIT'.

m_message.

  • >>>>> Begin of change note 575877

  • to kill the Excel process it's necessary to free all used objects

FREE OBJECT h_cell. m_message.

FREE OBJECT h_cell1. m_message.

FREE OBJECT range. m_message.

FREE OBJECT worksheet. m_message.

FREE OBJECT workbook. m_message.

FREE OBJECT application. m_message.

  • <<<<< End of change note 575877

ENDFUNCTION.

Reply
2 REPLIES 2
Read only

naveen_kumar116
Active Participant

‎2010 Sep 22 11:31 AM

0 Likes
720

Hi,

Please check the link below it will solve ur problem,

http://abap-explorer.blogspot.com/2008/12/upload-data-from-multiple-worksheets-of.html

/people/sap.user72/blog/2006/01/19/upload-data-from-multiple-worksheets-within-a-single-excel-file-into-internal-table-from-abap

http://www.sap-img.com/abap/uploading-multiple-multitab-excel-sheets-or-ranges.htm

Cheers,

Naveen

Read only

Former Member
In response to naveen_kumar116

‎2010 Sep 23 6:48 AM

0 Likes
720

Thanks for the links.

But I customized the standard FM(ALSM_EXCEL_TO_INTERNAL_TABLE) and included the codings which I included in the previous post (Marked inside #).

It reads the worksheet which I wanted correctly.

But the problem is, I couldn't kill the excel.exe in the taskbar.

When I run the program again it will create a another excel.exe....so on..