Skip to end of metadata
Go to start of metadata

The following code extracts information from SD tables VBAK, VBAP, VBEP and VBPA and inserts the results in separate worksheets of an excel spreadsheet.

Error rendering macro 'code': Invalid value specified for parameter 'com.atlassian.confluence.ext.code.render.InvalidValueException'
*&-------------------------------------------------------------------*
*& Report  Z_EXCEL_DOWNLOAD
*--------------------------------------------------------------------*
report   z_excel_download message-id zexcel.

*** Includes
* include: z_excel_download_top,    " global Data
*         z_excel_download_sel,    " Selection screen
*         z_excel_download_f01.    " FORM-Routines

*** Initialization
initialization.

*** Start-of-selection
start-of-selection.
end-of-selection.

*** Main program   
perform process_data.

*&---------------------------------------------------------------------*
*& Include Z_EXCEL_DOWNLOAD_TOP   Report Z_EXCEL_DOWNLOAD
*&---------------------------------------------------------------------*

*** Include objects
INCLUDE: OLE2INCL.

*** Table definitions
TABLES: VBAK,
        VBAP,
        VBPA,
        VBEP.

*** Constants
CONSTANTS: cns_09(2) TYPE n VALUE 09.

*** Type declarations
TYPES: BEGIN OF gty_excel1,
         vbeln TYPE vbeln,
         auart TYPE auart.
TYPES: END OF gty_excel1.

TYPES: BEGIN OF gty_excel2,
         vbeln TYPE vbeln,
         posnr TYPE posnr,
         matnr TYPE matnr.
TYPES: END OF gty_excel2.

TYPES: BEGIN OF gty_excel3,
         vbeln TYPE vbeln,
         posnr TYPE posnr,
         etenr TYPE etenr.
TYPES: END OF gty_excel3.

TYPES: BEGIN OF gty_excel4,
         vbeln TYPE vbeln,
         parvw TYPE parvw,
         kunnr type kunnr.
TYPES: END OF gty_excel4.

TYPES: data1(1500) TYPE c,
       ty_data     TYPE TABLE OF data1.

*** Variable Declarations
* Declare cells
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,
      h_sheet4    TYPE ole2_object,
      gs_font     TYPE ole2_object,
      flg_stop(1) TYPE c.

*** Work area and internal table definitions
DATA: gt_excel1 TYPE STANDARD TABLE OF gty_excel1,
      wa_excel1 TYPE gty_excel1,
      gt_excel2 TYPE STANDARD TABLE OF gty_excel2,
      wa_excel2 TYPE gty_excel2,
      gt_excel3 TYPE STANDARD TABLE OF gty_excel3,
      wa_excel3 TYPE gty_excel3,
      gt_excel4 TYPE STANDARD TABLE OF gty_excel4,
      wa_excel4 TYPE gty_excel4.

*** Variables
DATA: gt_1 TYPE ty_data WITH HEADER LINE,
      gt_2 TYPE ty_data WITH HEADER LINE,
      gt_3 TYPE ty_data WITH HEADER LINE,
      gt_4 TYPE ty_data WITH HEADER LINE,
      rec TYPE sy-tfill,
      deli(1) TYPE c,
      l_amt(18) TYPE c,
      gv_sheet_name(20) TYPE c.

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

*** Field Symbols
FIELD-SYMBOLS: <fs>.

*** Macro Definition
DEFINE ole_check_error.
  if &1 ne 0.
    message e000 with &1.
    exit.
  endif.
END-OF-DEFINITION.
*&---------------------------------------------------------------------*
*&  Include           Z_EXCEL_DOWNLOAD_SEL
*&---------------------------------------------------------------------*

select-options: so_vbeln for vbak-vbeln.
 
*&---------------------------------------------------------------------*
*&  Include           Z_EXCEL_DOWNLOAD_F01
*&---------------------------------------------------------------------*
*&---------------------------------------------------------------------*
*&      Form  PROCESS_DATA
*&---------------------------------------------------------------------*
FORM process_data .

  DATA l_rc TYPE i.

  deli = cl_abap_char_utilities=>horizontal_tab.

*** Popoluate tables
  SELECT vbeln auart INTO TABLE gt_excel1
    FROM vbak
    WHERE vbeln IN so_vbeln.

  SELECT vbeln posnr matnr INTO TABLE gt_excel2
    FROM vbap
    WHERE vbeln IN so_vbeln.

  SELECT vbeln posnr etenr INTO TABLE gt_excel3
    FROM vbep
    WHERE vbeln IN so_vbeln.

  SELECT vbeln parvw kunnr INTO TABLE gt_excel4
    FROM vbpa
    WHERE vbeln IN so_vbeln.

  LOOP AT gt_excel1 INTO wa_excel1.
    CONCATENATE wa_excel1-vbeln wa_excel1-auart INTO gt_1 SEPARATED BY deli.
    APPEND gt_1.
    CLEAR gt_1.
  ENDLOOP.

  LOOP AT gt_excel2 INTO wa_excel2.
    CONCATENATE wa_excel2-vbeln wa_excel2-posnr INTO gt_2 SEPARATED BY deli.
    APPEND gt_2.
    CLEAR gt_2.
  ENDLOOP.

  LOOP AT gt_excel3 INTO wa_excel3.
    CONCATENATE wa_excel3-vbeln wa_excel3-posnr wa_excel3-etenr INTO gt_3 SEPARATED BY deli.
    APPEND gt_3.
    CLEAR gt_3.
  ENDLOOP.

  LOOP AT gt_excel4 INTO wa_excel4.
    CONCATENATE wa_excel4-vbeln wa_excel4-parvw wa_excel4-kunnr INTO gt_4 SEPARATED BY deli.
    APPEND gt_4.
    CLEAR gt_4.
  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.

  PERFORM set_first_sheet.

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

  PERFORM paste_excel.

  perform create_sheet tables gt_2
                       using 'VBAP' h_sheet2.

  perform create_sheet tables gt_3
                       using 'VBEP' h_sheet3.

  perform create_sheet tables gt_4
                       using 'VBPA' h_sheet4.

* Free Excel objects
FREE OBJECT: h_zl,
             h_mapl,
             h_map,
             h_excel.

ENDFORM.                    " PROCESS_DATA
*&---------------------------------------------------------------------*
*&      Form  PASTE_EXCEL
*&---------------------------------------------------------------------*
FORM paste_excel .

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

ENDFORM.                    " PASTE_EXCEL
*&---------------------------------------------------------------------*
*&      Form  SET_FIRST_SHEET
*&---------------------------------------------------------------------*
FORM set_first_sheet .

  gv_sheet_name = 'VBAK'.
  GET PROPERTY OF h_excel   'ACTIVESHEET' = worksheet.
  SET PROPERTY OF worksheet 'Name'        = gv_sheet_name .

ENDFORM.                    " SET_FIRST_SHEET
*&---------------------------------------------------------------------*
*&      Form  CREATE_SHEET
*&---------------------------------------------------------------------*
form CREATE_SHEET  tables   it_sheet type ty_data
                   using    iv_name
                            iv_sheet TYPE ole2_object.

  DATA l_rc TYPE i.

  gv_sheet_name = iv_name.
  GET PROPERTY OF h_excel 'Sheets' = iv_sheet .
  CALL METHOD OF iv_sheet 'Add' = h_map.
  SET PROPERTY OF h_map 'Name' = gv_sheet_name .
  GET PROPERTY OF h_excel 'ACTIVESHEET' = worksheet.


  CALL METHOD cl_gui_frontend_services=>clipboard_export
    IMPORTING
      data                 = it_sheet[]
    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'.

endform.                    " CREATE_SHEET
 

2 Comments

  1. thanks ....... really helpful ...

  2. Second option of using of copy/past 

    DATA gs_chartAdoZet TYPE OLE2_OBJECT.


    GET PROPERTY OF gs_activesheet 'ChartObjects' gs_chartobjects.

           CALL METHOD OF gs_chartobjects 'Item' gs_chartAdoZet   " That method return object to chart

                 EXPORTING

                       #1 'Chart 1'.   " Index = Number (1)/String ('Chart 1') <- find out in excel Name Box Number 1, ... n

           CALL METHOD OF gs_chartAdoZet 'Activate' .

           CALL METHOD OF gs_chartAdoZet 'Select' .

           CALL METHOD OF gs_chartAdoZet 'Cut'               " 'Copy'/'Cut'.

     

    How does it to use ??? I was attaching according those link

    Generaly

    Object model (Excel VBA reference)

     

    Chart

    Chart Members (Excel)

     

    Typs of charts ( bottom at the end of www page) http://wiki.scn.sap.com/wiki/display/ABAP/ABAP-OLE+Code+Display+More+then+One+Chart+in+Excel