Excel routine 1

* Program source header: Cash Flow Statement
* Program name:
* Description:
* Date/author:
* Table update:
* Special logic:
* Include:
*-----------------------------------------------------------------------
* Modification log: Update record of program modification
*-----------------------------------------------------------------------
* Changedate programmer request description
* ===================================================== ======================================
*-----------------------------------------------------------------------
* Report Name: the program name and report format,
*-----------------------------------------------------------------------
Report zfi003
No standard page heading
Message-ID 00 "used message
Line-count 800 "Number of reports per page
Line-size 180. "report width per page
*-----------------------------------------------------------------------

* Table Description: The table used by the declaring Program
*-----------------------------------------------------------------------
Tables: bseg, bkpf, glt0.
*-----------------------------------------------------------------------
* Data: Declares the variables and custom types used by the program.
Include ole2incl. "For Ole
Data: Excel type ole2_object,
Books type ole2_object,
Sheet Type ole2_object,
Cell type ole2_object.
*-----------------------------------------------------------------------
Data: Begin of itab_bkpf occurs 0,
Belnr like BKPF-BELNR, "header-credential No.
End of itab_bkpf.

Data: Begin of itab_tt occurs 0,
Belnr like BSEG-BELNR, "credential No.
Hkont like BSEG-HKONT, "Table body-General Ledger
Rstgr like BSEG-RSTGR, "Reason code
Shkzg like BSEG-SHKZG, "debit and credit
Dmbtr like BSEG-DMBTR, "base currency amount
End of itab_tt.

Data: d01 like BSEG-DMBTR,
D02 like BSEG-DMBTR,
D03 like BSEG-DMBTR,
D04 like BSEG-DMBTR,
D05 like BSEG-DMBTR,
D06 like BSEG-DMBTR,
D07 like BSEG-DMBTR,
D08 like BSEG-DMBTR,
D09 like BSEG-DMBTR,
D10 like BSEG-DMBTR,
D11 like BSEG-DMBTR,
D12 like BSEG-DMBTR,
D13 like BSEG-DMBTR,
D14 like BSEG-DMBTR,
D15 like BSEG-DMBTR,
D16 like BSEG-DMBTR,
D17 like BSEG-DMBTR,
D18 like BSEG-DMBTR,
D19 like BSEG-DMBTR,
D20 like BSEG-DMBTR,
D21 like BSEG-DMBTR,
D22 like BSEG-DMBTR,
D23 like BSEG-DMBTR,
D24 like BSEG-DMBTR,
D25 like BSEG-DMBTR,
D26 like BSEG-DMBTR,
D27 like BSEG-DMBTR,
D28 like BSEG-DMBTR,
D29 like BSEG-DMBTR,
D30 like BSEG-DMBTR,
D31 like BSEG-DMBTR.

**----------------------------------------------------------------------
** Selection screen/option/parameter:
* Screen input report filtering Conditions
**----------------------------------------------------------------------
Selection-screen begin of block bl01 with frame title text-001.
* Parameters: p_bukrs like glt0-bukrs obligatory default '20140901 '.
Select-options: p_bukrs for GLT0-BUKRS obligatory default '20140901 '.
Select-options: s_gjahr for BKPF-GJAHR obligatory default SY-DATUM (4 ),
S_monat for BKPF-MONAT default SY-DATUM + 4 (2 ).
Selection-screen end of block bl01.

Selection-screen begin of block blk2 with frame title text-001.
Parameters: p_fname (60) default 'C: \ sap \ cash. xls '.
Selection-screen end of block blk2.
*----------------------------------------------------------------------
* At selection-screen:
* The event to be executed when the selection screen is to be left. You can check the input.
*----------------------------------------------------------------------
* At selection-screen.
* The if S_MONAT-HIGH is initial.
* Message e398 with 'enter the upper limit of the posting period! '.
* Endif.
*----------------------------------------------------------------------
* At start Selection: Enter the block to be started after completion,
* Press <F8>
*----------------------------------------------------------------------
Start-of-selection.
Data: l_exist.
Call function 'tmp _ gui_get_file_exist'
Exporting
Fname = p_fname
Importing
Exist = l_exist
* Isdir =
* Filesize =
Exceptions
Fileinfo_error = 1
Others = 2.
If SY-SUBRC <> 0 or l_exist <> 'x'. "SY-SUBRC return code value 0 indicating operation successful
Message i398 (00) with 'An error occurred while opening the Template File' p_fname! '.
Exit.
Endif.
Perform read_data.
*----------------------------------------------------------------------
* End of Selection: starts after data is printed,
* If the conditions for user input can be printed
*-----------------------------------------------------------------------
End-of-selection.
Perform write_bs.
* Form: subprograms used in the program
*-----------------------------------------------------------------------
* Read data: read data from a table into an internal table.
*-----------------------------------------------------------------------
Form read_data.

Select
Belnr "header-credential No.
Into corresponding fields of table itab_bkpf
From bkpf
Where gjahr in s_gjahr
And Monat in s_monat
And bukrs in p_bukrs.
Select
Hkont "Table body-total classified accounts
Rstgr "Reason code
Shkzg "debit and credit
Belnr "header-credential No.
Dmbtr "base currency amount
Into corresponding fields of table itab_tt
From bseg
Where gjahr in s_gjahr
And bukrs in p_bukrs
And hkont <= '000000 '.
Loop at itab_tt.
Read Table itab_bkpf with key belnr = ITAB_TT-BELNR.
If SY-SUBRC <> 0.
Delete itab_tt.
Endif.
Endloop.
Free itab_bkpf.
Loop at itab_tt.
Case ITAB_TT-RSTGR.
When '01 '.
If ITAB_TT-SHKZG = 'H '.
ITAB_TT-DMBTR = 0-ITAB_TT-DMBTR.
Endif.
D01 = d01 + ITAB_TT-DMBTR.
When '02 '.
If ITAB_TT-SHKZG = 'H '.
ITAB_TT-DMBTR = 0-ITAB_TT-DMBTR.
Endif.
D02 = d02 + ITAB_TT-DMBTR.
When '03 '.
If ITAB_TT-SHKZG = 'H '.
ITAB_TT-DMBTR = 0-ITAB_TT-DMBTR.
Endif.
D03 = d03 + ITAB_TT-DMBTR.
When '04 '.
If ITAB_TT-SHKZG ='s '.
ITAB_TT-DMBTR = 0-ITAB_TT-DMBTR.
Endif.
D05 = d05 + ITAB_TT-DMBTR.
When '05 '.
If ITAB_TT-SHKZG ='s '.
ITAB_TT-DMBTR = 0-ITAB_TT-DMBTR.
Endif.
D06 = d06 + ITAB_TT-DMBTR.
When '06 '.
If ITAB_TT-SHKZG ='s '.
ITAB_TT-DMBTR = 0-ITAB_TT-DMBTR.
Endif.
D07 = d07 + ITAB_TT-DMBTR.
When '07 '.
If ITAB_TT-SHKZG ='s '.
ITAB_TT-DMBTR = 0-ITAB_TT-DMBTR.
Endif.
D08 = d08 + ITAB_TT-DMBTR.
When '08 '.
If ITAB_TT-SHKZG = 'H '.
ITAB_TT-DMBTR = 0-ITAB_TT-DMBTR.
Endif.
D11 = D11 + ITAB_TT-DMBTR.
When '09 '.
If ITAB_TT-SHKZG = 'H '.
ITAB_TT-DMBTR = 0-ITAB_TT-DMBTR.
Endif.
D12 = D12 + ITAB_TT-DMBTR.
When '10 '.
If ITAB_TT-SHKZG = 'H '.
ITAB_TT-DMBTR = 0-ITAB_TT-DMBTR.
Endif.
D13 = D13 + ITAB_TT-DMBTR.
When '11 '.
If ITAB_TT-SHKZG = 'H '.
ITAB_TT-DMBTR = 0-ITAB_TT-DMBTR.
Endif.
D14 = D14 + ITAB_TT-DMBTR.
When '12 '.
If ITAB_TT-SHKZG ='s '.
ITAB_TT-DMBTR = 0-ITAB_TT-DMBTR.
Endif.
D16 = D16 + ITAB_TT-DMBTR.
When '13 '.
If ITAB_TT-SHKZG ='s '.
ITAB_TT-DMBTR = 0-ITAB_TT-DMBTR.
Endif.
D17 = d17 + ITAB_TT-DMBTR.
When '14 '.
If ITAB_TT-SHKZG ='s '.
ITAB_TT-DMBTR = 0-ITAB_TT-DMBTR.
Endif.
D18 = d18 + ITAB_TT-DMBTR.
When '15 '.
If ITAB_TT-SHKZG = 'H '.
ITAB_TT-DMBTR = 0-ITAB_TT-DMBTR.
Endif.
D21 = D21 + ITAB_TT-DMBTR.
When '16 '.
If ITAB_TT-SHKZG = 'H '.
ITAB_TT-DMBTR = 0-ITAB_TT-DMBTR.
Endif.
D22 = D22 + ITAB_TT-DMBTR.
When '17 '.
If ITAB_TT-SHKZG = 'H '.
ITAB_TT-DMBTR = 0-ITAB_TT-DMBTR.
Endif.
D23 = d23 + ITAB_TT-DMBTR.
When '18 '.
If ITAB_TT-SHKZG ='s '.
ITAB_TT-DMBTR = 0-ITAB_TT-DMBTR.
Endif.
D25 = D25 + ITAB_TT-DMBTR.
When '19 '.
If ITAB_TT-SHKZG ='s '.
ITAB_TT-DMBTR = 0-ITAB_TT-DMBTR.
Endif.
D26 = d26 + ITAB_TT-DMBTR.
When '20 '.
If ITAB_TT-SHKZG ='s '.
ITAB_TT-DMBTR = 0-ITAB_TT-DMBTR.
Endif.
D27 = d27 + ITAB_TT-DMBTR.
When '21 '.
If ITAB_TT-SHKZG = 'H '.
ITAB_TT-DMBTR = 0-ITAB_TT-DMBTR.
Endif.
D30 = d30 + ITAB_TT-DMBTR.
Endcase.
Endloop.

* D05 = ABS (d05 ).
* D06 = ABS (d06 ).
* D07 = ABS (d07 ).
* D08 = ABS (d08 ).
* D16 = ABS (D16 ).
* D17 = ABS (d17 ).
* D18 = ABS (d18 ).
* D25 = ABS (D25 ).
* D26 = ABS (d26 ).
* D27 = ABS (d27 ).

D04 = d01 + d02 + d03.
D09 = d05 + d06 + d07 + d08.
D10 = d04-d09.
D15 = D11 + D12 + D13 + d14.
D19 = D16 + d17 + d18.
D20 = D15-d19.
D24 = D21 + D22 + d23.
D28 = D25 + d26 + d27.
D29 = d24-d28.
D31 = D10 + d20 + D29 + d30.
Endform. "read_data

*&---------------------------------------------------------------------*
* & Form write_bs
*&---------------------------------------------------------------------*
* Text
*----------------------------------------------------------------------*
Form write_bs.
Create object Excel 'excel. application '.
Call method of Excel 'workbook' = books.
Call method of books 'open'
Exporting #1 = p_fname. "'C: \ cash. xls '.
Call method of Excel 'worksheets '= sheet exporting #1 = 1.
Call method of sheet 'activate '.
Call method of Excel 'cells '= cell exporting #1 = 1 #2 = 1.

Perform fill_cell using 6 3 d01.
Perform fill_cell using 7 3 d02.
Perform fill_cell using 8 3 d03.
Perform fill_cell using 9 3 d04.
Perform fill_cell using 10 3 d05.
Perform fill_cell using 11 3 d06.
Perform fill_cell using 12 3 d07.
Perform fill_cell using 13 3 d08.
Perform fill_cell using 14 3 d09.
Perform fill_cell using 15 3 D10.
Perform fill_cell using 17 3 d11.
Perform fill_cell using 18 3 D12.
Perform fill_cell using 19 3 d13.
Perform fill_cell using 20 3 d14.
Perform fill_cell using 21 3 d15.
Perform fill_cell using 22 3 D16.
Perform fill_cell using 23 3 d17.
Perform fill_cell using 24 3 d18.
Perform fill_cell using 25 3 d19.
Perform fill_cell using 26 3 d20.
Perform fill_cell using 28 3 d21.
Perform fill_cell using 29 3 d22.
Perform fill_cell using 30 3 d23.
Perform fill_cell using 31 3 d24.
Perform fill_cell using 32 3 d25.
Perform fill_cell using 33 3 d26.
Perform fill_cell using 34 3 d27.
Perform fill_cell using 35 3 d28.
Perform fill_cell using 36 3 d29.
Perform fill_cell using 37 3 d30.
Perform fill_cell using 38 3 d31.
Perform fill_cell using 39 3 s_monat.

Set property of Excel 'visible '= 1.

Endform. "fill_cell
*&---------------------------------------------------------------------*
* & Form fill_cell
*&---------------------------------------------------------------------*
* Text
*----------------------------------------------------------------------*
* --> P_ I text
* --> P_j text
* --> P_val text
*----------------------------------------------------------------------*
Form fill_cell using p_ I
P_j
P_val.
Call method of Excel 'cells '= cell exporting #1 = p_ I #2 = p_j.
Set property of cell 'value' = p_val.

Endform. "fill_cell