ABAP - Download Data Into More Than One Excel Sheet.

*&---------------------------------------------------------------------*

*& Report ZETA_EXCEL_DOWNLOAD_CLIPBOARD *
*& *
*&---------------------------------------------------------------------*
*& *
*& *
*&---------------------------------------------------------------------*

REPORT ZETA_EXCEL_DOWNLOAD_CLIPBOARD .

INCLUDE OLE2INCL.

DATA: w_cell1 TYPE ole2_object,
w_cell2 TYPE ole2_object.

*--- Ole data Declarations

DATA: h_excel TYPE ole2_object, " Excel object
h_mapl TYPE ole2_object, " list of workbooks
h_map TYPE ole2_object, " workbook
h_zl TYPE ole2_object, " cell
h_f TYPE ole2_object, " font
gs_interior TYPE ole2_object, " Pattern
worksheet TYPE ole2_object,
h_cell TYPE ole2_object,
h_cell1 TYPE ole2_object,
range TYPE ole2_object,
h_sheet2 TYPE ole2_object,
h_sheet3 TYPE ole2_object,
GS_FONT TYPE ole2_object,
flg_stop(1) TYPE c.

*********************************************************************
** Internal table Declaration
*********************************************************************

DATA: BEGIN OF T_EXCEL OCCURS 0,
VKORG(20) TYPE C, "Sales Org
VBTYP(20) TYPE C, "Document Category
AUART(20) TYPE C, "Document Type
ERNAM(20) TYPE C, "Created By
VBELN(20) TYPE C, "Document Number
POSNR(20) TYPE C, "Item Number
ERDAT(20) TYPE C, "Created Date
VDATU(20) TYPE C, "Header Requested Delivery Date
REQDAT(20) TYPE C, "Request date
CONDAT(20) TYPE C, "Confirm date
LIFSK(20) TYPE C, "Header Block
TXT30(30) TYPE C, "Order User Status Description
LIFSP(20) TYPE C, "Line Block
DISPO(20) TYPE C, "MRP Controller
DSNAM(20) TYPE C, "MRP Controller Description
VMSTA(20) TYPE C, "Material Sales Status
KUNNR(20) TYPE C, "Sold To
CNAME(35) TYPE C, "Sold To Name
REGIO(20) TYPE C, "State
CUFD(10) TYPE C, "CUD
BSTNK(20) TYPE C, "PO#
BSARK(20) TYPE C, "Ordering Method
MATNR(20) TYPE C, "Material
MAKTX(35) TYPE C, "Material Description
T200(20) TYPE C, "T200
VTEXT(20) TYPE C, "T200 Description
MATKL(20) TYPE C, "Material Group
ZZBOMIND(7) TYPE C, "BOM Indicator
OSTAT(20) TYPE C, "Order Status
CMGST(20) TYPE C, "CRD
INCO1(20) TYPE C, "Incoterms
OQTY(20) TYPE C, "Order Quantity
PQTY(20) TYPE C, "Open Quantity
UNIT(20) TYPE C, "UOM
ONET(20) TYPE C, "Order Value
PNET(20) TYPE C, "Open Value
CURR(20) TYPE C, "Currency key
SO_BEZEI LIKE TVKBT-BEZEI,"Sales Office
SG_BEZEI LIKE TVGRT-BEZEI,"Sales Group
BNAME(20) TYPE C, "Ordering Party
CONTACT(20) TYPE C, "Contact Name
TELF1(20) TYPE C, "Contact telf1
REQQTY(20) TYPE C, "Item Request qty
REQVAL(20) TYPE C, "Item Request value
CONQTY(20) TYPE C, "Item Confirm qty
CONVAL(20) TYPE C, "Item Confirm value
ZZREV(02) TYPE C, "Revenue recognition acceptance
BEZEI(20) TYPE C, "Revenue recognition text
VGBEL(20) TYPE C, "Reference Order for RETURNS
0008TEXT(255) TYPE C, "Internal Order Comment Text
END OF T_EXCEL.

DATA: T_EXCEL_BCKORD LIKE T_EXCEL OCCURS 0 WITH HEADER LINE,
T_EXCEL_BCKLOG LIKE T_EXCEL OCCURS 0 WITH HEADER LINE,
T_EXCEL_BLKORD LIKE T_EXCEL OCCURS 0 WITH HEADER LINE.

TYPES: data1(1500) TYPE c,

ty TYPE TABLE OF data1.

DATA: it TYPE ty WITH HEADER LINE,
it_2 TYPE ty WITH HEADER LINE,
it_3 TYPE ty WITH HEADER LINE,
rec TYPE sy-tfill,
deli(1) TYPE c,
l_amt(18) TYPE c.

DATA: BEGIN OF hex,
tab TYPE x,
END OF hex.

FIELD-SYMBOLS: .

CONSTANTS cns_09(2) TYPE n VALUE 09.

ASSIGN deli TO TYPE 'X'.
hex-tab = cns_09.
= hex-tab.

DATA GV_SHEET_NAME(20) TYPE C .
*----------------------------------------------------------------------*
* M A C R O Declaration
*----------------------------------------------------------------------*

DEFINE ole_check_error.

if &1 ne 0.
message e001(zz) with &1.
exit.
endif.

END-OF-DEFINITION.

T_EXCEL_BCKORD-vkorg = 'ABC'.
T_EXCEL_BCKORD-vbtyp = 'DEF'.
T_EXCEL_BCKORD-auart = 'GHI'.
T_EXCEL_BCKORD-ernam = 'JKL'.
T_EXCEL_BCKORD-vbeln = 'MNO'.
T_EXCEL_BCKORD-0008text = 'XYZ'.

APPEND T_EXCEL_BCKORD.

T_EXCEL_BCKORD-vkorg = 'ABC1'.
T_EXCEL_BCKORD-vbtyp = 'DEF1'.
T_EXCEL_BCKORD-auart = 'GHI1'.
T_EXCEL_BCKORD-ernam = 'JKL1'.
T_EXCEL_BCKORD-vbeln = 'MNO1'.
T_EXCEL_BCKORD-0008text = 'XYZ1'.

APPEND T_EXCEL_BCKORD.

T_EXCEL_BCKORD-vkorg = 'ABC2'.
T_EXCEL_BCKORD-vbtyp = 'DEF2'.
T_EXCEL_BCKORD-auart = 'GHI2'.
T_EXCEL_BCKORD-ernam = 'JKL2'.
T_EXCEL_BCKORD-vbeln = 'MNO2'.
T_EXCEL_BCKORD-0008text = 'XYZ2'.

APPEND T_EXCEL_BCKORD.

T_EXCEL_BCKLOG-vkorg = 'ABC'.
T_EXCEL_BCKLOG-vbtyp = 'DEF'.
T_EXCEL_BCKLOG-auart = 'GHI'.
T_EXCEL_BCKLOG-ernam = 'JKL'.
T_EXCEL_BCKLOG-vbeln = 'MNO'.
T_EXCEL_BCKLOG-0008text = 'XYZ'.

APPEND T_EXCEL_BCKLOG.

T_EXCEL_BCKLOG-vkorg = 'ABC1'.
T_EXCEL_BCKLOG-vbtyp = 'DEF1'.
T_EXCEL_BCKLOG-auart = 'GHI1'.
T_EXCEL_BCKLOG-ernam = 'JKL1'.
T_EXCEL_BCKLOG-vbeln = 'MNO1'.
T_EXCEL_BCKLOG-0008text = 'XYZ1'.

APPEND T_EXCEL_BCKLOG.

T_EXCEL_BCKLOG-vkorg = 'ABC2'.
T_EXCEL_BCKLOG-vbtyp = 'DEF2'.
T_EXCEL_BCKLOG-auart = 'GHI2'.
T_EXCEL_BCKLOG-ernam = 'JKL2'.
T_EXCEL_BCKLOG-vbeln = 'MNO2'.
T_EXCEL_BCKLOG-0008text = 'XYZ2'.

APPEND T_EXCEL_BCKLOG.

T_EXCEL_BCKLOG-vkorg = 'ABC3'.
T_EXCEL_BCKLOG-vbtyp = 'DEF3'..
T_EXCEL_BCKLOG-auart = 'GHI3'.
T_EXCEL_BCKLOG-ernam = 'JKL3'.
T_EXCEL_BCKLOG-vbeln = 'MNO3'.
T_EXCEL_BCKLOG-0008text = 'XYZ3'.

APPEND T_EXCEL_BCKLOG.

T_EXCEL_BLKORD-vkorg = 'ABC'.
T_EXCEL_BLKORD-vbtyp = 'DEF'.
T_EXCEL_BLKORD-auart = 'GHI'.
T_EXCEL_BLKORD-ernam = 'JKL'.
T_EXCEL_BLKORD-vbeln = 'MNO'.
T_EXCEL_BLKORD-0008text = 'XYZ'.

APPEND T_EXCEL_BLKORD.

T_EXCEL_BLKORD-vkorg = 'ABC1'.
T_EXCEL_BLKORD-vbtyp = 'DEF1'.
T_EXCEL_BLKORD-auart = 'GHI1'.
T_EXCEL_BLKORD-ernam = 'JKL1'.
T_EXCEL_BLKORD-vbeln = 'MNO1'.
T_EXCEL_BLKORD-0008text = 'XYZ1'.

APPEND T_EXCEL_BLKORD.

T_EXCEL_BLKORD-vkorg = 'ABC2'.
T_EXCEL_BLKORD-vbtyp = 'DEF2'.
T_EXCEL_BLKORD-auart = 'GHI2'.
T_EXCEL_BLKORD-ernam = 'JKL2'.
T_EXCEL_BLKORD-vbeln = 'MNO2'.
T_EXCEL_BLKORD-0008text = 'XYZ2'.

APPEND T_EXCEL_BLKORD.

T_EXCEL_BLKORD-vkorg = 'ABC3'.
T_EXCEL_BLKORD-vbtyp = 'DEF3'.
T_EXCEL_BLKORD-auart = 'GHI3'.
T_EXCEL_BLKORD-ernam = 'JKL3'.
T_EXCEL_BLKORD-vbeln = 'MNO3'.
T_EXCEL_BLKORD-0008text = 'XYZ3'.

APPEND T_EXCEL_BLKORD.

T_EXCEL_BLKORD-vkorg = 'ABC4'.
T_EXCEL_BLKORD-vbtyp = 'DEF4'..
T_EXCEL_BLKORD-auart = 'GHI4'.
T_EXCEL_BLKORD-ernam = 'JKL4'.
T_EXCEL_BLKORD-vbeln = 'MNO4'.
T_EXCEL_BLKORD-0008text = 'XYZ4'.

APPEND T_EXCEL_BLKORD.

LOOP AT T_EXCEL_BCKORD.

CONCATENATE

T_EXCEL_BCKORD-vkorg
T_EXCEL_BCKORD-vbtyp
T_EXCEL_BCKORD-auart
T_EXCEL_BCKORD-ernam
T_EXCEL_BCKORD-vbeln
T_EXCEL_BCKORD-posnr
T_EXCEL_BCKORD-erdat

T_EXCEL_BCKORD-vdatu
T_EXCEL_BCKORD-reqdat
T_EXCEL_BCKORD-condat
T_EXCEL_BCKORD-lifsk
T_EXCEL_BCKORD-txt30

T_EXCEL_BCKORD-lifsp
T_EXCEL_BCKORD-dispo
T_EXCEL_BCKORD-dsnam
T_EXCEL_BCKORD-vmsta
T_EXCEL_BCKORD-kunnr

T_EXCEL_BCKORD-cname
T_EXCEL_BCKORD-regio
T_EXCEL_BCKORD-cufd
T_EXCEL_BCKORD-bstnk
T_EXCEL_BCKORD-bsark
T_EXCEL_BCKORD-matnr
T_EXCEL_BCKORD-maktx
T_EXCEL_BCKORD-t200

T_EXCEL_BCKORD-vtext
T_EXCEL_BCKORD-matkl
T_EXCEL_BCKORD-zzbomind
T_EXCEL_BCKORD-ostat
T_EXCEL_BCKORD-cmgst

T_EXCEL_BCKORD-inco1
T_EXCEL_BCKORD-oqty
T_EXCEL_BCKORD-pqty
T_EXCEL_BCKORD-unit
T_EXCEL_BCKORD-onet

T_EXCEL_BCKORD-pnet
T_EXCEL_BCKORD-curr
T_EXCEL_BCKORD-so_bezei
T_EXCEL_BCKORD-sg_bezei
T_EXCEL_BCKORD-bname

T_EXCEL_BCKORD-contact
T_EXCEL_BCKORD-telf1
T_EXCEL_BCKORD-reqqty
T_EXCEL_BCKORD-reqval
T_EXCEL_BCKORD-conqty

T_EXCEL_BCKORD-conval
T_EXCEL_BCKORD-zzrev
T_EXCEL_BCKORD-bezei
T_EXCEL_BCKORD-vgbel
T_EXCEL_BCKORD-0008text

INTO it
SEPARATED BY deli.

APPEND it.
CLEAR it.

ENDLOOP.

LOOP AT T_EXCEL_BCKLOG.

CONCATENATE

T_EXCEL_BCKLOG-vkorg
T_EXCEL_BCKLOG-vbtyp
T_EXCEL_BCKLOG-auart
T_EXCEL_BCKLOG-ernam
T_EXCEL_BCKLOG-vbeln

T_EXCEL_BCKLOG-posnr
T_EXCEL_BCKLOG-erdat
T_EXCEL_BCKLOG-vdatu
T_EXCEL_BCKLOG-reqdat
T_EXCEL_BCKLOG-condat

T_EXCEL_BCKLOG-lifsk
T_EXCEL_BCKLOG-txt30
T_EXCEL_BCKLOG-lifsp
T_EXCEL_BCKLOG-dispo
T_EXCEL_BCKLOG-dsnam

T_EXCEL_BCKLOG-vmsta
T_EXCEL_BCKLOG-kunnr
T_EXCEL_BCKLOG-cname
T_EXCEL_BCKLOG-regio
T_EXCEL_BCKLOG-cufd

T_EXCEL_BCKLOG-bstnk
T_EXCEL_BCKLOG-bsark
T_EXCEL_BCKLOG-matnr
T_EXCEL_BCKLOG-maktx
T_EXCEL_BCKLOG-t200

T_EXCEL_BCKLOG-vtext
T_EXCEL_BCKLOG-matkl
T_EXCEL_BCKLOG-zzbomind
T_EXCEL_BCKLOG-ostat
T_EXCEL_BCKLOG-cmgst

T_EXCEL_BCKLOG-inco1
T_EXCEL_BCKLOG-oqty
T_EXCEL_BCKLOG-pqty
T_EXCEL_BCKLOG-unit
T_EXCEL_BCKLOG-onet

T_EXCEL_BCKLOG-pnet
T_EXCEL_BCKLOG-curr
T_EXCEL_BCKLOG-so_bezei
T_EXCEL_BCKLOG-sg_bezei
T_EXCEL_BCKLOG-bname

T_EXCEL_BCKLOG-contact
T_EXCEL_BCKLOG-telf1
T_EXCEL_BCKLOG-reqqty
T_EXCEL_BCKLOG-reqval
T_EXCEL_BCKLOG-conqty

T_EXCEL_BCKLOG-conval
T_EXCEL_BCKLOG-zzrev
T_EXCEL_BCKLOG-bezei
T_EXCEL_BCKLOG-vgbel
T_EXCEL_BCKLOG-0008text

INTO it_2
SEPARATED BY deli.

APPEND it_2.
CLEAR it_2.

ENDLOOP.

LOOP AT T_EXCEL_BLKORD.
CONCATENATE

T_EXCEL_BLKORD-vkorg
T_EXCEL_BLKORD-vbtyp
T_EXCEL_BLKORD-auart
T_EXCEL_BLKORD-ernam
T_EXCEL_BLKORD-vbeln

T_EXCEL_BLKORD-posnr
T_EXCEL_BLKORD-erdat
T_EXCEL_BLKORD-vdatu
T_EXCEL_BLKORD-reqdat
T_EXCEL_BLKORD-condat

T_EXCEL_BLKORD-lifsk
T_EXCEL_BLKORD-txt30
T_EXCEL_BLKORD-lifsp
T_EXCEL_BLKORD-dispo
T_EXCEL_BLKORD-dsnam

T_EXCEL_BLKORD-vmsta
T_EXCEL_BLKORD-kunnr
T_EXCEL_BLKORD-cname
T_EXCEL_BLKORD-regio
T_EXCEL_BLKORD-cufd

T_EXCEL_BLKORD-bstnk
T_EXCEL_BLKORD-bsark
T_EXCEL_BLKORD-matnr
T_EXCEL_BLKORD-maktx
T_EXCEL_BLKORD-t200

T_EXCEL_BLKORD-vtext
T_EXCEL_BLKORD-matkl
T_EXCEL_BLKORD-zzbomind
T_EXCEL_BLKORD-ostat
T_EXCEL_BLKORD-cmgst

T_EXCEL_BLKORD-inco1
T_EXCEL_BLKORD-oqty
T_EXCEL_BLKORD-pqty
T_EXCEL_BLKORD-unit
T_EXCEL_BLKORD-onet

T_EXCEL_BLKORD-pnet
T_EXCEL_BLKORD-curr
T_EXCEL_BLKORD-so_bezei
T_EXCEL_BLKORD-sg_bezei
T_EXCEL_BLKORD-bname
T_EXCEL_BLKORD-contact

T_EXCEL_BLKORD-telf1
T_EXCEL_BLKORD-reqqty
T_EXCEL_BLKORD-reqval
T_EXCEL_BLKORD-conqty
T_EXCEL_BLKORD-conval

T_EXCEL_BLKORD-zzrev

T_EXCEL_BLKORD-bezei
T_EXCEL_BLKORD-vgbel
T_EXCEL_BLKORD-0008text

INTO it_3
SEPARATED BY deli.
APPEND it_3.
CLEAR it_3.

ENDLOOP.

* start Excel

IF h_excel-header = space OR h_excel-handle = -1.
CREATE OBJECT h_excel 'EXCEL.APPLICATION'.
ENDIF.

*--- get list of workbooks, initially empty

CALL METHOD OF h_excel 'Workbooks' = h_mapl.
SET PROPERTY OF h_excel 'Visible' = 1.

CALL METHOD OF h_mapl 'Add' = h_map.
GV_SHEET_NAME = 'Back Orders'.

GET PROPERTY OF h_excel 'ACTIVESHEET' = worksheet.
SET PROPERTY OF worksheet 'Name' = GV_SHEET_NAME .

*--Formatting the area of additional data 1 and doing the BOLD

CALL METHOD OF H_EXCEL 'Cells' = w_CELL1
EXPORTING
#1 = 1
#2 = 1.

CALL METHOD OF H_EXCEL 'Cells' = W_CELL2
EXPORTING
#1 = 1
#2 = 50.

CALL METHOD OF H_EXCEL 'Range' = H_CELL
EXPORTING
#1 = W_CELL1
#2 = W_CELL2.

GET PROPERTY OF H_CELL 'Font' = GS_FONT .
SET PROPERTY OF GS_FONT 'Bold' = 1 .

DATA l_rc TYPE i.

CALL METHOD cl_gui_frontend_services=>clipboard_export
IMPORTING
data = it[]
CHANGING
rc = l_rc
EXCEPTIONS
cntl_error = 1
error_no_gui = 2
not_supported_by_gui = 3
OTHERS = 4.

CALL METHOD OF h_excel 'Cells' = w_cell1
EXPORTING
#1 = 1
#2 = 1.

CALL METHOD OF h_excel 'Cells' = w_cell2
EXPORTING
#1 = 1
#2 = 1.

CALL METHOD OF h_excel 'Range' = range
EXPORTING
#1 = w_cell1
#2 = w_cell2.

CALL METHOD OF range 'Select'.

CALL METHOD OF worksheet 'Paste'.

GV_SHEET_NAME = 'Backlog'.
GET PROPERTY OF H_EXCEL 'Sheets' = h_sheet2 .

CALL METHOD OF h_sheet2 'Add' = h_map.

SET PROPERTY OF h_map 'Name' = GV_SHEET_NAME .
GET PROPERTY OF h_excel 'ACTIVESHEET' = worksheet.

*--Formatting the area of additional data 1 and doing the BOLD

CALL METHOD OF H_EXCEL 'Cells' = w_CELL1
EXPORTING
#1 = 1
#2 = 1.

CALL METHOD OF H_EXCEL 'Cells' = W_CELL2
EXPORTING
#1 = 1
#2 = 50.

CALL METHOD OF H_EXCEL 'Range' = H_CELL
EXPORTING
#1 = W_CELL1
#2 = W_CELL2.

GET PROPERTY OF H_CELL 'Font' = GS_FONT .
SET PROPERTY OF GS_FONT 'Bold' = 1 .

CALL METHOD cl_gui_frontend_services=>clipboard_export
IMPORTING
data = it_2[]
CHANGING
rc = l_rc
EXCEPTIONS
cntl_error = 1
error_no_gui = 2
not_supported_by_gui = 3
OTHERS = 4.

CALL METHOD OF h_excel 'Cells' = w_cell1
EXPORTING
#1 = 1
#2 = 1.

CALL METHOD OF h_excel 'Cells' = w_cell2
EXPORTING
#1 = 1
#2 = 1.

CALL METHOD OF h_excel 'Range' = range
EXPORTING
#1 = w_cell1
#2 = w_cell2.

CALL METHOD OF range 'Select'.

CALL METHOD OF worksheet 'Paste'.

GV_SHEET_NAME = 'Blocked Orders'.
GET PROPERTY OF H_EXCEL 'Sheets' = h_sheet3 .

CALL METHOD OF h_sheet3 'Add' = h_map.

SET PROPERTY OF h_map 'Name' = GV_SHEET_NAME .
GET PROPERTY OF h_excel 'ACTIVESHEET' = worksheet.

*--Formatting the area of additional data 1 and doing the BOLD

CALL METHOD OF H_EXCEL 'Cells' = w_CELL1
EXPORTING
#1 = 1
#2 = 1.

CALL METHOD OF H_EXCEL 'Cells' = W_CELL2
EXPORTING
#1 = 1
#2 = 50.

ALSO READ:

- Download A File With The Specified Delimit Character Dynamically.

- Download ABAP Source Code & TEXTPOOL To The Desktop.

- Downloading Programs Into A Folder In Presentation Server.

- Download SAP Data In XML Format.

- Download SAPScript Output To PDF File.

RETURN TO MAIN INDEX:

- Sample Programs On Uploading & Downloading Files.

- Sample Programs On HR ABAP.

- Sample Report Programs On ALV List/ Grid Display.

- Sample Programs On Selection Screen.

- Sample Programs On BDC.

.....Back To MAIN INDEX.