topic Re: Upload Data from multiple sheets into different table from Excel workbook in Application Development and Automation Discussions

Upload Data from multiple sheets into different table from Excel workbook

Former Member — Thu, 16 Nov 2006 10:31:05 GMT

Hi,

I need to upload data from multiple sheets of an Excel workbook into different internal tables. I went through quite a number of posts, but could not get working code.

Could you provide me with some related code & documentation?

Thanks..

Re: Upload Data from multiple sheets into different table from Excel workbook

Former Member — Thu, 16 Nov 2006 10:34:27 GMT

Read this link:

Regards,

Ravi

Re: Upload Data from multiple sheets into different table from Excel workbook

Former Member — Thu, 16 Nov 2006 10:39:18 GMT

Use the below code...

&----

*& Report ZNEGI9 *

*& *

&----

*& *

&----

REPORT ZNEGI9 NO STANDARD PAGE HEADING.

INCLUDE ole2incl .

DATA: gs_excel TYPE ole2_object ,

gs_wbooklist TYPE ole2_object ,

gs_application TYPE ole2_object ,

gs_wbook TYPE ole2_object ,

gs_activesheet TYPE ole2_object ,

gs_sheets TYPE ole2_object ,

gs_newsheet TYPE ole2_object ,

gs_cell1 TYPE ole2_object ,

gs_cell2 TYPE ole2_object ,

gs_cells TYPE ole2_object ,

gs_range TYPE ole2_object ,

gs_font TYPE ole2_object ,

gs_interior TYPE ole2_object ,

gs_columns TYPE ole2_object ,

gs_charts TYPE ole2_object ,

gs_chart TYPE ole2_object ,

gs_charttitle TYPE ole2_object ,

gs_charttitlechar TYPE ole2_object ,

gs_chartobjects TYPE ole2_object .

DATA gv_sheet_name(20) TYPE c .

DATA gv_outer_index LIKE sy-index .

DATA gv_intex(2) TYPE c .

DATA gv_line_cntr TYPE i . "line counter

DATA gv_linno TYPE i . "line number

DATA gv_colno TYPE i . "column number

DATA gv_value TYPE i . "data

PARAMETERS: p_sheets TYPE i .

START-OF-SELECTION .

DO p_sheets TIMES .

*--Forming sheet name

gv_intex = sy-index .

gv_outer_index = sy-index .

CONCATENATE 'Excel Sheet #' gv_intex INTO gv_sheet_name .

*--For the first loop, Excel is initiated and one new sheet is added

IF sy-index = 1 .

CREATE OBJECT gs_excel 'EXCEL.APPLICATION' .

SET PROPERTY OF gs_excel 'Visible' = 1 .

GET PROPERTY OF gs_excel 'Workbooks' = gs_wbooklist .

GET PROPERTY OF gs_wbooklist 'Application' = gs_application .

SET PROPERTY OF gs_application 'SheetsInNewWorkbook' = 1 .

CALL METHOD OF gs_wbooklist 'Add' = gs_wbook .

GET PROPERTY OF gs_application 'ActiveSheet' = gs_activesheet .

SET PROPERTY OF gs_activesheet 'Name' = gv_sheet_name .

*--For the rest of loops, other sheets are added

ELSE .

GET PROPERTY OF gs_wbook 'Sheets' = gs_sheets .

CALL METHOD OF gs_sheets 'Add' = gs_newsheet .

SET PROPERTY OF gs_newsheet 'Name' = gv_sheet_name .

ENDIF .

gv_line_cntr = 1 . "line counter

*--Title

*--Selecting cell area to be merged.

CALL METHOD OF gs_excel 'Cells' = gs_cell1

EXPORTING

#1 = 1

#2 = 1.

CALL METHOD OF gs_excel 'Cells' = gs_cell2

EXPORTING

#1 = 1

#2 = 4.

CALL METHOD OF gs_excel 'Range' = gs_cells

EXPORTING

#1 = gs_cell1

#2 = gs_cell2.

CALL METHOD OF gs_cells 'Select' .

*--Merging

CALL METHOD OF gs_cells 'Merge' .

*--Setting title data

CALL METHOD OF gs_excel 'Cells' = gs_cell1

EXPORTING

#1 = gv_line_cntr

#2 = 1.

SET PROPERTY OF gs_cell1 'Value' = 'KISHAN' .

*--Formatting the title

GET PROPERTY OF gs_cell1 'Font' = gs_font .

SET PROPERTY OF gs_font 'Underline' = 2 .

SET PROPERTY OF gs_font 'Bold' = 1 .

SET PROPERTY OF gs_cell1 'HorizontalAlignment' = -4108 .

GET PROPERTY OF gs_cell1 'Interior' = gs_interior .

SET PROPERTY OF gs_interior 'ColorIndex' = 15 .

SET PROPERTY OF gs_interior 'Pattern' = -4124 .

SET PROPERTY OF gs_interior 'PatternColorIndex' = -4105 .

gv_line_cntr = gv_line_cntr + 1 .

*--Writing some additional data for the title

CALL METHOD OF gs_excel 'Cells' = gs_cell1

EXPORTING

#1 = gv_line_cntr

#2 = 1.

SET PROPERTY OF gs_cell1 'Value' = 'Sheet No' .

CALL METHOD OF gs_excel 'Cells' = gs_cell1

EXPORTING

#1 = gv_line_cntr

#2 = 5.

SET PROPERTY OF gs_cell1 'Value' = ':' .

CALL METHOD OF gs_excel 'Cells' = gs_cell1

EXPORTING

#1 = gv_line_cntr

#2 = 6.

SET PROPERTY OF gs_cell1 'Value' = gv_intex .

*--Formatting the area of additional data 1

CALL METHOD OF gs_excel 'Cells' = gs_cell1

EXPORTING

#1 = 1

#2 = 1.

CALL METHOD OF gs_excel 'Cells' = gs_cell2

EXPORTING

#1 = gv_line_cntr

#2 = 5.

CALL METHOD OF gs_excel 'Range' = gs_cells

EXPORTING

#1 = gs_cell1

#2 = gs_cell2.

CALL METHOD OF gs_cells 'Select' .

GET PROPERTY OF gs_cells 'Font' = gs_font .

SET PROPERTY OF gs_font 'Bold' = 1 .

*--Formatting the area of additional data 2

CALL METHOD OF gs_excel 'Cells' = gs_cell1

EXPORTING

#1 = 1

#2 = 5.

CALL METHOD OF gs_excel 'Cells' = gs_cell2

EXPORTING

#1 = gv_line_cntr

#2 = 5.

CALL METHOD OF gs_excel 'Range' = gs_cells

EXPORTING

#1 = gs_cell1

#2 = gs_cell2.

CALL METHOD OF gs_cells 'Select' .

GET PROPERTY OF gs_cells 'Columns' = gs_columns .

CALL METHOD OF gs_columns 'AutoFit' .

*--Bordering title data area

CALL METHOD OF gs_excel 'Cells' = gs_cell1

EXPORTING

#1 = 1

#2 = 1.

CALL METHOD OF gs_excel 'Cells' = gs_cell2

EXPORTING

#1 = gv_line_cntr

#2 = 6.

CALL METHOD OF gs_excel 'Range' = gs_cells

EXPORTING

#1 = gs_cell1

#2 = gs_cell2.

CALL METHOD OF gs_cells 'Select' .

CALL METHOD OF gs_cells 'BorderAround'

EXPORTING

#1 = 1 "continuous line

#2 = 4. "thick

*--Putting axis labels

gv_colno = 2 .

gv_line_cntr = gv_line_cntr + 5 .

gv_linno = gv_line_cntr - 1 .

CALL METHOD OF gs_excel 'Cells' = gs_cell1

EXPORTING

#1 = gv_linno

#2 = 1.

SET PROPERTY OF gs_cell1 'Value' = 'X' .

CALL METHOD OF gs_excel 'Cells' = gs_cell1

EXPORTING

#1 = gv_line_cntr

#2 = 1.

SET PROPERTY OF gs_cell1 'Value' = 'Y' .

*--Generating some data

DO 3 TIMES .

gv_value = gv_outer_index * sy-index * 10 .

CALL METHOD OF gs_excel 'Cells' = gs_cell1

EXPORTING

#1 = gv_linno

#2 = gv_colno.

SET PROPERTY OF gs_cell1 'Value' = sy-index .

CALL METHOD OF gs_excel 'Cells' = gs_cell1

EXPORTING

#1 = gv_line_cntr

#2 = gv_colno.

SET PROPERTY OF gs_cell1 'Value' = gv_value .

gv_colno = gv_colno + 1 .

ENDDO .

*--Source data area

gv_colno = gv_colno - 1 .

CALL METHOD OF gs_excel 'Cells' = gs_cell1

EXPORTING #1 = gv_linno

#2 = 1.

CALL METHOD OF gs_excel 'Cells' = gs_cell2

EXPORTING #1 = gv_line_cntr

#2 = gv_colno.

CALL METHOD OF gs_excel 'Range' = gs_cells

EXPORTING #1 = gs_cell1

#2 = gs_cell2.

CALL METHOD OF gs_cells 'Select' .

GET PROPERTY OF gs_application 'Charts' = gs_charts .

CALL METHOD OF gs_charts 'Add' = gs_chart .

CALL METHOD OF gs_chart 'Activate' .

SET PROPERTY OF gs_chart 'ChartType' = '51' . "Vertical bar graph

CALL METHOD OF gs_chart 'SetSourceData'

EXPORTING #1 = gs_cells

#2 = 1.

SET PROPERTY OF gs_chart 'HasTitle' = 1 .

GET PROPERTY OF gs_chart 'ChartTitle' = gs_charttitle .

GET PROPERTY OF gs_charttitle 'Characters' = gs_charttitlechar .

SET PROPERTY OF gs_charttitlechar 'Text' = 'Sample Graph' .

*--Locate the chart onto the current worksheet

*--Activate current sheet

CALL METHOD OF gs_excel 'WorkSheets' = gs_activesheet

EXPORTING #1 = gv_sheet_name.

CALL METHOD OF gs_activesheet 'Activate' .

CALL METHOD OF gs_chart 'Location'

EXPORTING #1 = 2

#2 = gv_sheet_name.

*--Reposition the chart on the worksheet (cut&paste)

CALL METHOD OF gs_activesheet 'ChartObjects' = gs_chartobjects .

CALL METHOD OF gs_chartobjects 'Select' .

CALL METHOD OF gs_chartobjects 'Cut' .

*--Select new area

gv_line_cntr = gv_line_cntr + 2 .

CALL METHOD OF gs_excel 'Cells' = gs_cell1

EXPORTING

#1 = gv_line_cntr

#2 = 1.

CALL METHOD OF gs_excel 'Cells' = gs_cell2

EXPORTING

#1 = gv_line_cntr

#2 = 1.

CALL METHOD OF gs_excel 'Range' = gs_cells

EXPORTING

#1 = gs_cell1

#2 = gs_cell2.

CALL METHOD OF gs_cells 'Select' .

CALL METHOD OF gs_activesheet 'Paste' .

enddo.

*--Deallocating memory

FREE: gs_excel, gs_wbooklist, gs_application, gs_wbook,

gs_activesheet,gs_sheets, gs_newsheet, gs_cell1,

gs_cell2, gs_cells, gs_range, gs_font, gs_interior,

gs_columns, gs_charts, gs_chart, gs_charttitle,

gs_charttitlechar, gs_chartobjects .

Re: Upload Data from multiple sheets into different table from Excel workbook

Simha_ — Thu, 16 Nov 2006 10:40:53 GMT

Hi,

Go through this code....

FUNCTION Z_MIRZA_UPLOADING_FROM_2SHEETS.

*"----

""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

Function Module To Upload Data From

Excel File Into Two Internal Tables

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

Function Module To Upload Data From

Excel File Into Two Internal Tables

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.

Function Module To Upload Data From

Excel File Into Two Internal Tables

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.

Function Module To Upload Data From

Excel File Into Two Internal Tables

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.

Function Module To Upload Data From

Excel File Into Two Internal Tables

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.

Function Module To Upload Data From

Excel File Into Two Internal Tables

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.

Function Module To Upload Data From

Excel File Into Two Internal Tables

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.

Function Module To Upload Data From

Excel File Into Two Internal Tables

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

Function Module To Upload Data From

Excel File Into Two Internal Tables

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.

cheers,

simha.

Re: Upload Data from multiple sheets into different table from Excel workbook

Former Member — Thu, 16 Nov 2006 10:44:34 GMT

Hi,

Thanks for your replies.

But my problem is of uploading the data into SAP from Excel sheet.

Thanks

Re: Upload Data from multiple sheets into different table from Excel workbook

Former Member — Thu, 16 Nov 2006 10:51:16 GMT

good

try this program

REPORT ZS00_EXCEL_OLE_TEST .

Marc Hoffmann HSE Consulting LLC, OrionFoodSystems LLC
Written on 46B in 2003

P_EXVIS EXCEL visible
P_WORKBK Number of workbooks to create
P_WSHEET Number of sheets per workbook

parameters: p_fname like RLGRAP-FILENAME

default 'C:\temp\testNN.xls'.

data: fname like p_fname,

kn like sy-repid.

data: cnt type i value 0.

data: sheetname(10) value 'TEST ',c_row type i,

scnt type i,

val(20), wb(2).

parameters: p_exvis as checkbox default 'X',

p_workbk(2) type p default '01',

p_wsheet(2) type p default '01'.

CONSTANTS: OK TYPE I VALUE 0.

INCLUDE OLE2INCL.

DATA: EXCEL TYPE OLE2_OBJECT,

WORKBOOK TYPE OLE2_OBJECT,

SHEET TYPE OLE2_OBJECT,

CELL TYPE OLE2_OBJECT,

CELL1 TYPE OLE2_OBJECT,

COLUMN TYPE OLE2_OBJECT,

RANGE TYPE OLE2_OBJECT,

BORDERS TYPE OLE2_OBJECT,

button TYPE OLE2_OBJECT,

int TYPE OLE2_OBJECT,

FONT TYPE OLE2_OBJECT,

ROW TYPE OLE2_OBJECT.

data: application type ole2_object,

book type ole2_object,

books type ole2_object.

data: ole_book TYPE ole2_object.

do p_workbk times.

move p_fname to fname.

unpack sy-index to wb.

replace 'NN' with wb into fname.

perform create_EXCEL.

create sheets and save

perform sheet.

perform save_book.

enddo.

write: ' Done'.

----

FORM create_excel *

----

........ *

----

form create_excel.

CREATE OBJECT EXCEL 'EXCEL.APPLICATION'.

if sy-subrc ne 0.

write: / 'No EXCEL creation possible'.

stop.

endif.

set property of EXCEL 'DisplayAlerts' = 0.

CALL METHOD OF EXCEL 'WORKBOOKS' = WORKBOOK .

Put Excel in background

if p_exvis eq 'X'.

SET PROPERTY OF EXCEL 'VISIBLE' = 1.

else.

SET PROPERTY OF EXCEL 'VISIBLE' = 0.

endif.

Create worksheet

set property of excel 'SheetsInNewWorkbook' = 1.

call method of workbook 'ADD'.

endform.

----

FORM save_book *

----

........ *

----

form save_book.

get property of excel 'ActiveSheet' = sheet.

free object sheet.

free object workbook.

GET PROPERTY OF EXCEL 'ActiveWorkbook' = WORKBOOK.

call method of workbook 'SAVEAS' exporting #1 = p_fname #2 = 1.

call method of workbook 'CLOSE'.

call method of excel 'QUIT'.

free object sheet.

free object workbook.

free object excel.

endform.

----

FORM sheet *

----

........ *

----

form sheet.

do p_wsheet times.

unpack sy-index to sheetname+5(2).

if sy-index gt 1.

CALL METHOD OF EXCEL 'WORKSHEETS' = sheet.

call method of sheet 'ADD'.

free object sheet.

endif.

scnt = sy-index.

call method of excel 'WORKSHEETS' = SHEET EXPORTING #1 = scnt.

call method of sheet 'ACTIVATE'.

SET PROPERTY OF SHEET 'NAME' = sheetname.

free object sheet. "OK

perform fill_sheet.

CALL METHOD OF EXCEL 'Columns' = COLUMN.

CALL METHOD OF COLUMN 'Autofit'.

free object COLUMN.

call method of sheet 'BUTTON' = button.
call method of button 'ADD'.
set property of button 'fmButtonStyle' = 0.
exporting #1 = '96.75' #2 = '372' #3 = '123.75' #4 = '12'.
set property of button 'Characters' = 'ButtonTest'.

free object button.

free object font.

free object int.

free object cell.

free object: cell1.

free object range.

free object borders.

free object: column, row.

enddo.

free object font.

free object int.

free object cell.

free object cell1.

free object range.

free object borders.

free object column.

free object row.

free object sheet.

endform.

----

FORM border *

----

........ *

----

--> we *

----

form border using we.

*left

call method of CELL 'BORDERS' = BORDERS exporting #1 = '1'.

set property of borders 'LineStyle' = '1'.

set property of borders 'WEIGHT' = we. "4=max

free object borders.

right

call method of CELL 'BORDERS' = BORDERS exporting #1 = '2'.

set property of borders 'LineStyle' = '2'.

set property of borders 'WEIGHT' = we.

free object borders.

call method of CELL 'BORDERS' = BORDERS exporting #1 = '3'.

set property of borders 'LineStyle' = '3'.

set property of borders 'WEIGHT' = we.

free object borders.

bottom

call method of CELL 'BORDERS' = BORDERS exporting #1 = '4'.

set property of borders 'LineStyle' = '4'.

set property of borders 'WEIGHT' = we.

set property of borders 'ColorIndex' = 'xlAutomatic'.

free object borders.

endform.

----

FORM border2 *

----

........ *

----

--> we *

----

form border2 using we.

*left

call method of CELL 'BORDERS' = BORDERS exporting #1 = '1'.

set property of borders 'LineStyle' = '5'.

set property of borders 'WEIGHT' = we. "4=max

free object borders.

right

call method of CELL 'BORDERS' = BORDERS exporting #1 = '2'.

set property of borders 'LineStyle' = '6'.

set property of borders 'WEIGHT' = we.

free object borders.

call method of CELL 'BORDERS' = BORDERS exporting #1 = '3'.

set property of borders 'LineStyle' = '7'.

set property of borders 'WEIGHT' = we.

free object borders.

bottom

call method of CELL 'BORDERS' = BORDERS exporting #1 = '4'.

set property of borders 'LineStyle' = '8'.

set property of borders 'WEIGHT' = we.

set property of borders 'ColorIndex' = 'xlAutomatic'.

free object borders.

endform.

----

FORM border3 *

----

........ *

----

--> we *

----

form border3 using we.

*left

call method of CELL 'BORDERS' = BORDERS exporting #1 = '1'.

set property of borders 'LineStyle' = '9'.

set property of borders 'WEIGHT' = we. "4=max

free object borders.

right

call method of CELL 'BORDERS' = BORDERS exporting #1 = '2'.

set property of borders 'LineStyle' = '10'.

set property of borders 'WEIGHT' = we.

free object borders.

call method of CELL 'BORDERS' = BORDERS exporting #1 = '3'.

set property of borders 'LineStyle' = '11'.

set property of borders 'WEIGHT' = we.

free object borders.

bottom

call method of CELL 'BORDERS' = BORDERS exporting #1 = '4'.

set property of borders 'LineStyle' = '12'.

set property of borders 'WEIGHT' = we.

set property of borders 'ColorIndex' = 'xlAutomatic'.

free object borders.

endform.

----

FORM fill_cell *

----

........ *

----

--> color *
--> pattern *

----

form fill_cell using color pattern.

call method of cell 'INTERIOR' = int.

set property of int 'ColorIndex' = color.

set property of int 'Pattern' = pattern.

free object int.

endform.

----

FORM font *

----

........ *

----

--> bold *
--> size *

----

form font using bold size.

call method of CELL 'FONT' = font.

set property of font 'BOLD' = bold.

set property of font 'SIZE' = size.

free object font.

endform.

----

FORM fill_sheet *

----

........ *

----

form fill_sheet.

CALL METHOD OF EXCEL 'RANGE' = CELL EXPORTING #1 = 'A1'.

perform font using 1 '14'.

SET PROPERTY OF CELL 'VALUE' = 'Counter'.

perform fill_cell using '15' '1'.

perform border using '2'.

free object cell.

val = 'Workbook-Count'.

move wb to val+16.

CALL METHOD OF EXCEL 'RANGE' = CELL EXPORTING #1 = 'B1'.

SET PROPERTY OF CELL 'VALUE' = val.

perform fill_cell using '14' '1'.

perform border using '4'.

free object cell.

val = 'Sheet-Count'.

unpack sy-index to val+12.

CALL METHOD OF EXCEL 'RANGE' = CELL EXPORTING #1 = 'C1'.

SET PROPERTY OF CELL 'VALUE' = val.

perform fill_cell using '12' '1'.

perform border using '4'.

free object cell.

CALL METHOD OF EXCEL 'RANGE' = CELL EXPORTING #1 = 'E3'.

perform border using '1'.

free object cell.

CALL METHOD OF EXCEL 'RANGE' = CELL EXPORTING #1 = 'E5'.

perform border using '2'.

free object cell.

CALL METHOD OF EXCEL 'RANGE' = CELL EXPORTING #1 = 'E7'.

perform border using '3'.

free object cell.

CALL METHOD OF EXCEL 'RANGE' = CELL EXPORTING #1 = 'E9'.

perform border using '4'.

free object cell.

CALL METHOD OF EXCEL 'RANGE' = CELL EXPORTING #1 = 'F3'.

perform border2 using '1'.

free object cell.

CALL METHOD OF EXCEL 'RANGE' = CELL EXPORTING #1 = 'F5'.

perform border2 using '2'.

free object cell.

CALL METHOD OF EXCEL 'RANGE' = CELL EXPORTING #1 = 'F7'.

perform border2 using '3'.

free object cell.

CALL METHOD OF EXCEL 'RANGE' = CELL EXPORTING #1 = 'F9'.

perform border2 using '4'.

free object cell.

CALL METHOD OF EXCEL 'RANGE' = CELL EXPORTING #1 = 'G3'.

perform border3 using '1'.

free object cell.

CALL METHOD OF EXCEL 'RANGE' = CELL EXPORTING #1 = 'G5'.

perform border3 using '2'.

free object cell.

CALL METHOD OF EXCEL 'RANGE' = CELL EXPORTING #1 = 'G7'.

perform border3 using '3'.

free object cell.

CALL METHOD OF EXCEL 'RANGE' = CELL EXPORTING #1 = 'G9'.

perform border3 using '4'.

free object cell.

val = 'ROW-Count'.

do 19 times.

c_row = sy-index + 1.

unpack c_row to val+12(4).

CALL METHOD OF excel 'CELLS' = CELL1 EXPORTING #1 = c_row #2 = 2.

SET PROPERTY OF CELL1 'VALUE' = val.

free object cell1.

CALL METHOD OF excel 'CELLS' = CELL1 EXPORTING #1 = c_row #2 = 4.

SET PROPERTY OF CELL1 'VALUE' = val.

free object cell1.

enddo.

endform.

----

You find SAP OLE programs under development Class 'SOLE' *
*
MSTAPPL Table Maintenance APPL *
RSOLEDOC Document list *
RSOLEIN0 OLE Load Type Information *
RSOLEINT Type Info Loaded *
RSOLETI0 OLE Object Browser *
RSOLETI1 OLE Object Browser *
RSOLETI2 OLE Object Browser *
RSOLETI3 F4 Help For OLE Objects *
RSOLETT1 OLE 2.0 Automation Demo Program *
*
Transactions: *
SOLE *
SOLO - List of OLE applcations with loaded type info *
*
*
You will find the decription of possible objects and methods in the *
windows help file for excel. *

----

thanks

mrutyun^

Re: Upload Data from multiple sheets into different table from Excel workbook

Former Member — Thu, 16 Nov 2006 11:02:18 GMT

Guys,

I have an Excel Workbook with multiple sheet & I need to upload data from them.

Thanks

Re: Upload Data from multiple sheets into different table from Excel workbook

Former Member — Thu, 16 Nov 2006 11:08:30 GMT

Hi,

Check this Article

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

also the code sample..

Regards

Vijay

Re: Upload Data from multiple sheets into different table from Excel workbook

Former Member — Thu, 16 Nov 2006 11:16:20 GMT

Hi Munish,

check this..

Regards

Vijay