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: 

TO UPLOAD DATA FROM 2 EXCEL SHEETS INTO TWO INTERNAL TABLES

Former Member
1,699

Hi,

I have worked out & developed an function module which I think is helpful to one and all.

TO UPLOAD DATA FROM TWO EXCEL SHEETS INTO TWO INTERNAL TABLES:

CODE:

*"----


""Local interface:

*" IMPORTING

*" VALUE(FILE_NAME) LIKE RLGRAP-FILENAME

*" VALUE(START_ROW_SHEET1) TYPE I

*" VALUE(START_COLUMN_SHEET1) TYPE I

*" VALUE(START_ROW_SHEET2) TYPE I

*" VALUE(START_COLUMN_SHEET2) TYPE I

*" VALUE(END_ROW_SHEET1) TYPE I

*" VALUE(END_COLUMN_SHEET1) TYPE I

*" VALUE(END_ROW_SHEET2) TYPE I

*" VALUE(END_COLUMN_SHEET2) TYPE I

*" TABLES

*" IT_DATA1 STRUCTURE ALSMEX_TABLINE

*" IT_DATA2 STRUCTURE ALSMEX_TABLINE

*" EXCEPTIONS

*" INCONSISTENT_PARAMETERS

*" UPLOAD_OLE

*"----


  • DATA DECLARATION

DATA: excel_tab TYPE ty_t_sender,

excel_tab1 TYPE ty_t_sender.

DATA: ld_separator TYPE c.

DATA: application TYPE ole2_object,

workbook TYPE ole2_object,

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

  • MESSAGE DEFINATION

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.

  • PARAMETER CHECK

IF START_ROW_SHEET1 > END_ROW_SHEET1.

RAISE inconsistent_parameters.

ENDIF.

IF START_COLUMN_SHEET1 > END_COLUMN_SHEET1.

RAISE inconsistent_parameters.

ENDIF.

IF START_ROW_SHEET2 > END_ROW_SHEET2.

RAISE inconsistent_parameters.

ENDIF.

IF START_COLUMN_SHEET2 > END_COLUMN_SHEET2.

RAISE inconsistent_parameters.

ENDIF.

CLASS cl_abap_char_utilities DEFINITION LOAD.

ld_separator = cl_abap_char_utilities=>horizontal_tab.

  • OPENING EXCEL FILE

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 application 'Workbooks' = workbook.

m_message.

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

m_message.

CALL METHOD OF APPLICATION 'Worksheets' = SHEET EXPORTING #1 = 1.

m_message.

CALL METHOD OF APPLICATION 'Worksheets' = SHEET EXPORTING #1 = 1.

m_message.

CALL METHOD OF SHEET 'Activate'.

m_message.

GET PROPERTY OF application 'ACTIVESHEET' = sheet.

m_message.

  • MARKING OF WHOLE SPREADSHEET

CALL METHOD OF sheet 'Cells' = h_cell

EXPORTING #1 = START_ROW_SHEET1 #2 = START_COLUMN_SHEET1.

m_message.

CALL METHOD OF sheet 'Cells' = h_cell1

EXPORTING #1 = END_ROW_SHEET1 #2 = END_COLUMN_SHEET1.

m_message.

CALL METHOD OF sheet 'RANGE' = range

EXPORTING #1 = h_cell #2 = h_cell1.

m_message.

CALL METHOD OF range 'SELECT'.

m_message.

  • Copy marked area (SHEET1) 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 IT_DATA1

USING ld_separator.

  • Clear the 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

.

  • Working in Second Excel Work Sheet

CALL METHOD OF APPLICATION 'Worksheets' = SHEET EXPORTING #1 = 2.

m_message.

CALL METHOD OF SHEET 'Activate'.

m_message.

GET PROPERTY OF application 'ACTIVESHEET' = sheet.

m_message.

  • Mark Sheet2

CALL METHOD OF sheet 'Cells' = h_cell

EXPORTING #1 = START_ROW_SHEET2 #2 = START_COLUMN_SHEET2.

m_message.

CALL METHOD OF sheet 'Cells' = h_cell1

EXPORTING #1 = END_ROW_SHEET2 #2 = END_COLUMN_SHEET2.

m_message.

CALL METHOD OF sheet 'RANGE' = range

EXPORTING #1 = h_cell #2 = h_cell1.

m_message.

CALL METHOD OF range 'SELECT'.

m_message.

  • Copy Marked Area (Sheet2) into Clippboard

CALL METHOD OF range 'COPY'.

m_message.

  • Read Clipboard into ABAP

CALL METHOD cl_gui_frontend_services=>clipboard_import

IMPORTING

data = excel_tab1

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_tab1 IT_DATA2

USING ld_separator.

  • Clear Clipboard

REFRESH excel_tab.

CALL METHOD cl_gui_frontend_services=>clipboard_export

IMPORTING

data = excel_tab1

CHANGING

rc = ld_rc

EXCEPTIONS

cntl_error = 1

  • ERROR_NO_GUI = 2

  • NOT_SUPPORTED_BY_GUI = 3

OTHERS = 4

  • Leaving Application

.

CALL METHOD OF application 'QUIT'.

m_message.

FREE OBJECT application.

m_message.

ENDFUNCTION.

FOR MORE INFORMATION YOU CAN email OR CALL ME:

MIRZA IFTHEKHAR BAIG

Consultant

KTree Computer Solutions India (P) Ltd.

email: mirza_ifthekhar@rediffmail.com

Mob: 09391121038

3 REPLIES 3

Former Member
0 Kudos
87

Hi Mirza,

Please place this code in SDN code samples.

Lanka

Former Member
0 Kudos
87

Hello Mirza,

I have got something exactly like this to do, uploading 2 excel files into 2 internal tables. I've tried to implement it but there are certain things I don't know how to declare, like the excel_tab type ty_t_sender. Its not found in the DDIC.

There are other things like CLASS cl_abap_char_utilities DEFINITION LOAD.

Please do help me.

I badly need this.

Thanks.

Regards,

Meera

0 Kudos
87

Hi Meera,

I apologise for delay. Anyways you can goto the

following link:

https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/docs/library/uuid/36f08758-0a01-0010-c291-c03...

for details.

Hope this will clear you. However, you further require

any more information then please feel free to mail.

Note:

ty_t_sender, cl_abap_char_utilities DEFINITION LOAD

all these are present in alsmex function group if you

place your function module in this function group then

you'll be able to access these things. Further, like

if you have security options and can't place your code

in this function group then create your own function

group in your own package and copy the contents from

alsmex function group.

Hope that answer your questions.

Regards,

Mirza Ifthekhar Baig.