Skip to Content
author's profile photo Former Member
0
Former Member
Jan 12, 2006 at 05:20 PM

TO UPLOAD DATA FROM 2 EXCEL SHEETS INTO TWO INTERNAL TABLES

1545 Views

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