Upload data from an Excel Workbook

[Former Member]

Hi Guys,

I've an Excel workbook with multiple sheets. I need to upload the data from all these sheets into SAP. I've read quite a number of posts but was unable to perform the task.

Could you please provide me with some working code & documentation?

Thanks

[Former Member]

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