Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

download to excel in two sheets

jeanclaudelhote
Explorer

‎02-27-2006 2:04 PM

0 Kudos

Hi,

we would like to download an internal table in an Excel sheet and the selection screen of the report in another sheet of the same file

is it possible ? and if yes how to do that

Thanks in advance

6 REPLIES 6

Former Member

‎02-27-2006 2:07 PM

0 Kudos

Collect the selection-screen information into one internal table.

Then pass the info(internal table) to

GUI_DOWNLOAD to get in xl sheet.

Former Member

‎02-27-2006 2:17 PM

0 Kudos

Hi ,

check this sample code...

REPORT z_excel.

data: begin of itab occurs 0,

vbeln like vbak-vbeln,

posnr like vbap-posnr,

end of itab.

select vbeln

posnr

from vbap

up to 20 rows

into table itab.

  • EXCEL sheet using OLE automation.

INCLUDE OLE2INCL.

  • handles for OLE objects

DATA: H_EXCEL TYPE OLE2_OBJECT, " Excel object

H_WORK TYPE OLE2_OBJECT,

H_SHEET TYPE OLE2_OBJECT,

H_CELL TYPE OLE2_OBJECT,

V_COL LIKE SY-TABIX. " column number of the cell

DATA:

V_STEP(30),

V_FILE LIKE RLGRAP-FILENAME.

  • tell user what is going on

CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'

EXPORTING

  • PERCENTAGE = 0

TEXT = 'Creating Excel...'

EXCEPTIONS

OTHERS = 1.

  • start Excel

V_STEP = 'Starting Excel'.

CREATE OBJECT H_EXCEL 'EXCEL.APPLICATION'.

PERFORM ERR_HDL.

SET PROPERTY OF H_EXCEL 'Visible' = 1.

  • CALL METHOD OF H_EXCEL 'OPEN' EXPORTING #1 = 'C:\DMC_REC.XLS'.

  • PERFORM ERR_HDL.

  • tell user what is going on

CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'

EXPORTING

  • PERCENTAGE = 0

TEXT = 'Adding Data to Excel...'

EXCEPTIONS

OTHERS = 1.

  • Get the list of workbooks

V_STEP = 'Preaparing Excel'.

CALL METHOD OF H_EXCEL 'WORKBOOKS' = H_WORK.

PERFORM ERR_HDL.

    • Add new workbook (create a file)

CALL METHOD OF H_WORK 'ADD'.

PERFORM ERR_HDL.

  • Get the created worksheet

CALL METHOD OF H_EXCEL 'WORKSHEETS' = H_SHEET EXPORTING #1 = 1.

PERFORM ERR_HDL.

  • Activate (select) the first sheet

CALL METHOD OF H_SHEET 'ACTIVATE'.

PERFORM ERR_HDL.

  • tell user what is going on

CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'

EXPORTING

  • PERCENTAGE = 0

TEXT = 'Adding Data to Excel...'

EXCEPTIONS

OTHERS = 1.

  • output column headings to active Excel sheet

V_STEP = 'Adding data to Excel'.

LOOP AT ITAB.

V_COL = SY-TABIX.

PERFORM FILL_CELL USING 1 V_COL ITAB-vbeln.

PERFORM FILL_CELL USING 2 V_COL ITAB-posnr.

ENDLOOP.

V_STEP = 'Releasing Excel'.

FREE OBJECT H_EXCEL.

PERFORM ERR_HDL.

H_EXCEL-HANDLE = -1.

&----

*& Form ERR_HDL

&----

  • text

----

  • --> p1 text

----

FORM ERR_HDL.

IF SY-SUBRC <> 0.

WRITE: / 'Error in processing Excel File:', V_STEP.

STOP.

ENDIF.

ENDFORM. " ERR_HDL

&----

*& Form FILL_CELL

&----

  • text

----

  • -->P_1 text

  • -->P_1 text

  • -->P_1 text

----

FORM FILL_CELL USING ROW COL VAL.

CALL METHOD OF H_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

Regards,

GSR.

former_member188685
Active Contributor

‎02-27-2006 2:18 PM

0 Kudos

Hi,

using OLE you can do that...

for the selection give different work book, for itab give different work book.

REPORT ztest_excel.
data: begin of itab occurs 0,
      vbeln like vbak-vbeln,
      posnr like vbap-posnr,
      end of itab.
select vbeln
       posnr
       from vbap
       up to 20 rows
       into table itab.
 
* EXCEL sheet using OLE automation.
INCLUDE OLE2INCL.
* handles for OLE objects
DATA: H_EXCEL TYPE OLE2_OBJECT,        " Excel object
      H_WORK  TYPE OLE2_OBJECT,
      H_SHEET TYPE OLE2_OBJECT,
      H_CELL  TYPE OLE2_OBJECT,
      V_COL   LIKE SY-TABIX.     " column number of the cell
DATA:
  V_STEP(30),
  V_FILE LIKE RLGRAP-FILENAME.
 
 
* tell user what is going on
  CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
     EXPORTING
*           PERCENTAGE = 0
           TEXT       = 'Creating Excel...'
       EXCEPTIONS
            OTHERS     = 1.
* start Excel
  V_STEP = 'Starting Excel'.
  CREATE OBJECT H_EXCEL 'EXCEL.APPLICATION'.
  PERFORM ERR_HDL.
 
  SET PROPERTY OF H_EXCEL  'Visible' = 1.
 
*  CALL METHOD OF H_EXCEL 'OPEN' EXPORTING  #1 = 'C:DMC_REC.XLS'.
 
*  PERFORM ERR_HDL.
* tell user what is going on
  CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
     EXPORTING
*           PERCENTAGE = 0
           TEXT       = 'Adding Data to Excel...'
       EXCEPTIONS
            OTHERS     = 1.
* Get the list of workbooks
  V_STEP = 'Preaparing Excel'.
  CALL METHOD OF H_EXCEL 'WORKBOOKS' = H_WORK.
  PERFORM ERR_HDL.
 
** Add new workbook (create a file)
  <b>CALL METHOD OF H_WORK 'ADD'.</b>
  <b>PERFORM ERR_HDL.</b>
* Get the created worksheet
  CALL METHOD OF H_EXCEL 'WORKSHEETS' = H_SHEET EXPORTING #1 = 1.
  PERFORM ERR_HDL.
* Activate (select) the first sheet
  CALL METHOD OF H_SHEET 'ACTIVATE'.
  PERFORM ERR_HDL.
 
 
* tell user what is going on
  CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
     EXPORTING
*           PERCENTAGE = 0
           TEXT       = 'Adding Data to Excel...'
       EXCEPTIONS
            OTHERS     = 1.
* output column headings to active Excel sheet
  V_STEP = 'Adding data to Excel'.
  LOOP AT ITAB.
    V_COL = SY-TABIX.
    PERFORM FILL_CELL USING 1 V_COL ITAB-vbeln.
    PERFORM FILL_CELL USING 2 V_COL ITAB-posnr.
  ENDLOOP.
  V_STEP = 'Releasing Excel'.
  FREE OBJECT H_EXCEL.
  PERFORM ERR_HDL.
  H_EXCEL-HANDLE = -1.
 
*&---------------------------------------------------------------------*
*&      Form  ERR_HDL
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*----------------------------------------------------------------------*
FORM ERR_HDL.
 
  IF SY-SUBRC <> 0.
    WRITE: / 'Error in processing Excel File:', V_STEP.
    STOP.
  ENDIF.
 
ENDFORM.                    " ERR_HDL
*&---------------------------------------------------------------------*
*&      Form  FILL_CELL
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      -->P_1      text
*      -->P_1      text
*      -->P_1      text
*----------------------------------------------------------------------*
FORM FILL_CELL USING  ROW COL VAL.
  CALL METHOD OF H_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

check out the bold ones, like that you need to add for selection screen.

Regards

Vijay

Former Member

‎02-27-2006 2:21 PM

0 Kudos

Hi Jean,

Yeah it is possible...Please see the below link for

the same.

<a href="http://www.sapdevelopment.co.uk/ms/ms_excel.htm">http://www.sapdevelopment.co.uk/ms/ms_excel.htm</a>

Thanks&Regards,

Siri.

jeanclaudelhote
Explorer
In response to Former Member

‎02-27-2006 2:29 PM

0 Kudos

oK

Thanks to all for your help !

former_member188685
Active Contributor
In response to Former Member

‎02-27-2006 2:33 PM

0 Kudos

Hi,

please Reward if any of the Replies are useful..

Regards

vijay