SAP R/3 форум ABAP консультантов
Russian ABAP Developer's Club

Home - FAQ - Search - Memberlist - Usergroups - Profile - Log in to check your private messages - Register - Log in - English
Blogs - Weblogs News

OLE/2 demo. Extracts information on accounts in GL



 
Post new topic   Reply to topic    Russian ABAP Developer's Club Forum Index -> OLE2, Excel, WinWord
View previous topic :: View next topic  
Author Message
admin
Администратор
Администратор



Joined: 01 Sep 2007
Posts: 1639

PostPosted: Fri Jan 22, 2010 4:12 pm    Post subject: OLE/2 demo. Extracts information on accounts in GL Reply with quote

Code:
*&---------------------------------------------------------------------*
*& Report  ZOLETEST                                                    *
*&                                                                     *
*&---------------------------------------------------------------------*
*&  OLE/2 demo.                                                        *
*&  Program extracts information on accounts in GL                     *
*&  (c) Miroslav Samoilenko, Price Waterhouse Moscow                   *
*&---------------------------------------------------------------------*

REPORT  ZOLETEST                      .

TABLES SKAT. " Descriptions for accounts
TABLES T001. " Master data for company codes
TABLES SKB1. " Master data for accounts (company code)

TYPES:
  BEGIN OF GT_DATA,  " type to hold data about an account
    KTOPL LIKE T001-KTOPL,  " chart of accounts
    BUKRS LIKE T001-BUKRS,  " company code
    BUTXT LIKE T001-BUTXT,  " description of company code
    SAKNR LIKE SKB1-SAKNR,  " account number
  END OF GT_DATA.


INCLUDE OLE2INCL.
DATA: XLAPP TYPE OLE2_OBJECT,     " Excel application OLE object
      WORKBOOK TYPE OLE2_OBJECT,  " Excel workbook OLE object
      SHEET TYPE OLE2_OBJECT,     " Excel worksheet OLE object
      CELLS TYPE OLE2_OBJECT.     " Excel cell OLE object

DATA CURRENTLINE TYPE I.          " Current line in spreadsheet
DATA INT_DATA TYPE GT_DATA        " Table of accounts
         OCCURS 1000 WITH HEADER LINE.

SELECT-OPTIONS S_KTOPL FOR T001-KTOPL.
SELECT-OPTIONS S_BUKRS FOR T001-BUKRS.

* Create an Excel application
CREATE OBJECT XLAPP 'excel.application'.
PERFORM ERRORS.

* Make Excel application visible
SET PROPERTY OF XLAPP 'Visible' = 1.
PERFORM ERRORS.

* Get the list of workbooks
CALL METHOD OF XLAPP 'Workbooks' = WORKBOOK.
PERFORM ERRORS.

* Add new workbook (create a file)
CALL METHOD OF WORKBOOK 'Add'.
PERFORM ERRORS.

* Get the created worksheet
CALL METHOD OF XLAPP 'Worksheets' = SHEET EXPORTING #1 = 1.
PERFORM ERRORS.

* Activate (select) the first sheet
CALL METHOD OF SHEET 'Activate'.
PERFORM ERRORS.

CURRENTLINE = 1.

* We need to select data from the database into an internal table
* because OLE Automation seems to call COMMIT WORK which is
* forbidden within a SELECT loop.

SELECT A~KTOPL A~BUKRS A~BUTXT B~SAKNR " select necessary fields
  INTO TABLE INT_DATA                  " into an internal table
  FROM T001 AS A INNER JOIN SKB1 AS B  " from Ccode and Account masters
  ON ( A~BUKRS = B~BUKRS               " tables are linked through
       AND A~MANDT = B~MANDT )         " company code
  WHERE A~KTOPL IN S_KTOPL
    AND A~BUKRS IN S_BUKRS
  ORDER BY A~KTOPL A~BUKRS B~SAKNR.    " sort records by CoA and CCode

* go through selected data
LOOP AT INT_DATA.

* when CoA is changed, print out CoA header
ON CHANGE OF INT_DATA-KTOPL.
 PERFORM NEWCHART.
ENDON.

* when CCode is changed, print out CCode header
ON CHANGE OF INT_DATA-BUKRS.
 PERFORM NEWCCODE.
ENDON.

* print out an account
 PERFORM NEWACCOUNT.

IF SY-SUBRC NE 0.
 EXIT.
ENDIF.

ENDLOOP.

* clean up memory
FREE OBJECT XLAPP.

******************************************************************
* Outputs current OLE error
*****************************************************************
FORM ERRORS.
 IF SY-SUBRC NE 0.
  WRITE: / 'Error in OLE call', SY-MSGLI.
  EXIT.
 ENDIF.
ENDFORM.

*****************************************************************
* Prints out the CoA header
*****************************************************************
FORM NEWCHART.
* Output CoA header into report
 WRITE:/ 'Chart of accounts', INT_DATA-KTOPL.

* Get current cell on the worksheet
 CALL METHOD OF SHEET 'Cells' = CELLS
         EXPORTING #1 = CURRENTLINE #2 = 1.
 PERFORM ERRORS.
* Output standard text into the selected cell
 SET PROPERTY OF CELLS 'Value' = 'Chart of accounts:'.
 PERFORM ERRORS.

* Get the next cell
 CALL METHOD OF SHEET 'Cells' = CELLS
         EXPORTING #1 = CURRENTLINE #2 = 2.
 PERFORM ERRORS.
* Output current CoA into the selected cell
 SET PROPERTY OF CELLS 'Value' = INT_DATA-KTOPL.
 PERFORM ERRORS.

* Move to the next line in the spreadsheet
 ADD 1 TO CURRENTLINE.
ENDFORM.

*****************************************************************
* Prints out the CCode header
*****************************************************************
FORM NEWCCODE.
 WRITE:/ 'Company Code:', INT_DATA-BUKRS, INT_DATA-BUTXT.

 CALL METHOD OF SHEET 'Cells' = CELLS
         EXPORTING #1 = CURRENTLINE #2 = 2.
 PERFORM ERRORS.
 SET PROPERTY OF CELLS 'Value' = 'Company Code:'.
 PERFORM ERRORS.

 CALL METHOD OF SHEET 'Cells' = CELLS
         EXPORTING #1 = CURRENTLINE #2 = 3.
 PERFORM ERRORS.
 SET PROPERTY OF CELLS 'Value' = INT_DATA-BUKRS.
 PERFORM ERRORS.

 CALL METHOD OF SHEET 'Cells' = CELLS
         EXPORTING #1 = CURRENTLINE #2 = 4.
 PERFORM ERRORS.
 SET PROPERTY OF CELLS 'Value' = INT_DATA-BUTXT.
 PERFORM ERRORS.

 ADD 1 TO CURRENTLINE.

ENDFORM.

*****************************************************************
* Prints out account information
*****************************************************************
FORM NEWACCOUNT.
 WRITE:/ 'Account number:', INT_DATA-SAKNR.
* get description for the current account number
 SELECT SINGLE * FROM SKAT
   WHERE KTOPL = INT_DATA-KTOPL
     AND SAKNR = INT_DATA-SAKNR
     AND SPRAS = SY-LANGU.
 WRITE: SKAT-TXT20.

 CALL METHOD OF SHEET 'Cells' = CELLS
         EXPORTING #1 = CURRENTLINE #2 = 4.
 PERFORM ERRORS.
 SET PROPERTY OF CELLS 'Value' = INT_DATA-SAKNR.
 PERFORM ERRORS.

 CALL METHOD OF SHEET 'Cells' = CELLS
         EXPORTING #1 = CURRENTLINE #2 = 5.
 PERFORM ERRORS.
 SET PROPERTY OF CELLS 'Value' = SKAT-TXT20.
 PERFORM ERRORS.

 ADD 1 TO CURRENTLINE.

ENDFORM.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    Russian ABAP Developer's Club Forum Index -> OLE2, Excel, WinWord All times are GMT + 4 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
You cannot attach files in this forum
You can download files in this forum


All product names are trademarks of their respective companies. SAPNET.RU websites are in no way affiliated with SAP AG.
SAP, SAP R/3, R/3 software, mySAP, ABAP, BAPI, xApps, SAP NetWeaver and any other are registered trademarks of SAP AG.
Every effort is made to ensure content integrity. Use information on this site at your own risk.