Hi,
Check the code and link it will be helpful to u
http://sap.ittoolbox.com/code/archives.asp?d=3027&a=s&i=10
-
report zuploadexcelsheets.
include ole2incl .
tables : vbap, vbak.
data: begin of itab occurs 0,
posnr like vbap-posnr,
vbeln like vbak-vbeln,
end of itab.
select vbeln
posnr
from vbap
up to 20 rows
into corresponding fields of table itab.
data : v_col like sy-tabix. " column number of the cell
data : v_row like sy-tabix.
data : zmeng like vbap-zmeng.
data : posnr1 like vbap-posnr.
data : count(3) type n.
data: gs_excel type ole2_object ,
h_cell 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 .
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
*PARAMETERS: p_sheets TYPE i .
data : p_sheets type i .
start-of-selection .
sort itab by posnr.
loop at itab to 1.
posnr1 = itab-posnr.
endloop.
count = 1.
loop at itab.
at new posnr.
p_sheets = p_sheets + 1.
endat.
endloop.
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
sort itab by posnr.
loop at itab where posnr = posnr1.
v_col = v_col + 1.
perform fill_cell using v_col 1 itab-vbeln.
perform fill_cell using v_col 2 itab-posnr.
zmeng = zmeng + itab-posnr.
at end of posnr.
v_col = v_col + 1.
perform fill_cell using v_col 2 zmeng.
clear v_row.
clear zmeng.
endat.
endloop.
delete itab where posnr = posnr1.
loop at itab to 1.
posnr1 = itab-posnr.
endloop.
clear v_col.
enddo.
*--Deallocating memory
free: gs_excel, gs_wbooklist, gs_application, gs_wbook,
gs_activesheet,gs_sheets, gs_newsheet.
form fill_cell using row col val.
call method of gs_excel 'Cells' = h_cell
exporting #1 = row #2 = col.
perform err_hdl.
set property of h_cell 'Value' = val .
perform err_hdl.
endform. " FILL_CELL
form err_hdl.
if sy-subrc <> 0.
write: / 'Error in processing Excel File:'.
stop.
endif.
endform.
Bluesky
