Community
Application Development and Automation 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: 
Read only

Plz Help.

Go to solution
Former Member

‎2008 Feb 18 6:04 AM

0 Likes
501

It is possible to download details from SAP into MS Excel

format. But is it possible to open Excel sheet within SAP? If yes how?

1 ACCEPTED SOLUTION
Read only

Go to solution
Former Member

‎2008 Feb 18 6:09 AM

0 Likes
476

>Yes, you can open excel sheet within SAP. The following steps will help you achieve the same.

>>1. Click (Change layout...). The Change layout box will open.

>>2. Click the View tab.

>>3. Choose the Excel option.

>>4. Click on sap_om.xls in the Template list that displays on

the right side of the box.

>>5. Click (Transfer).

>>6. Microsoft Excel will open in the window where the report

data was displayed. You can now use all of the options within

Microsoft Excel and SAP to format and manipulate the data as

necessary.

>>7. To save the file, follow the Excel menu path File > Save

Copy As.

>>8. To close Excel, click (Change layout...), click the View

tab, choose the Grid option, and click

(Transfer).

2 REPLIES 2
Read only

Go to solution
Former Member

‎2008 Feb 18 6:09 AM

0 Likes
476

Check the following code for that purpose : 



REPORT ztest1.

* Written on 46B in 2003
*
* P_EXVIS	EXCEL visible
* P_WORKBK	Number of workbooks to create
* P_WSHEET	Number of sheets per workbook
*
parameters: p_fname like RLGRAP-FILENAME
             default 'D:\testNN.xls'.

data: fname like p_fname,
      kn like sy-repid.
data: cnt type i value 0.


data: sheetname(10) value 'TEST ',c_row type i,
      scnt type i,
      val(20), wb(2).

parameters: p_exvis as checkbox default 'X',
            p_workbk(2) type p default '01',
            p_wsheet(2) type p default '01'.




CONSTANTS: OK TYPE I VALUE 0.
INCLUDE OLE2INCL.
DATA: EXCEL     TYPE OLE2_OBJECT,
      WORKBOOK  TYPE OLE2_OBJECT,
      SHEET     TYPE OLE2_OBJECT,
      CELL      TYPE OLE2_OBJECT,
      CELL1     TYPE OLE2_OBJECT,
      COLUMN    TYPE OLE2_OBJECT,
      RANGE     TYPE OLE2_OBJECT,
      BORDERS   TYPE OLE2_OBJECT,
      button    TYPE OLE2_OBJECT,
      int      TYPE OLE2_OBJECT,
      FONT      TYPE OLE2_OBJECT,
      ROW       TYPE OLE2_OBJECT.

data: application type ole2_object,
      book        type ole2_object,
      books       type ole2_object.
data: ole_book    TYPE ole2_object.





do p_workbk times.
  move p_fname to fname.
  unpack sy-index to wb.
  replace 'NN' with wb into fname.
*
  perform create_EXCEL.

* create sheets and save
  perform sheet.

  perform save_book.
enddo.
write: ' Done'.


*---------------------------------------------------------------------*
*       FORM create_excel                                             *
*---------------------------------------------------------------------*
*       ........                                                      *
*---------------------------------------------------------------------*
form create_excel.
  CREATE OBJECT EXCEL 'EXCEL.APPLICATION'.

  if sy-subrc ne 0.
     write: / 'No EXCEL creation possible'.
     stop.
  endif.

  set property of EXCEL 'DisplayAlerts' = 0.

  CALL METHOD  OF EXCEL 'WORKBOOKS' = WORKBOOK .
*  Put Excel in background
  if p_exvis eq 'X'.
    SET PROPERTY OF EXCEL 'VISIBLE' = 1.
  else.
    SET PROPERTY OF EXCEL 'VISIBLE' = 0.
  endif.

*  Create worksheet
  set property of excel 'SheetsInNewWorkbook' = 1.
  call method of workbook   'ADD'.
endform.



*---------------------------------------------------------------------*
*       FORM save_book                                                *
*---------------------------------------------------------------------*
*       ........                                                      *
*---------------------------------------------------------------------*
form save_book.
  get property of excel 'ActiveSheet' = sheet.
  free object sheet.
  free object workbook.

  GET PROPERTY OF EXCEL 'ActiveWorkbook' = WORKBOOK.
  call method of workbook 'SAVEAS' exporting #1 = p_fname #2 = 1.
  call method of workbook 'CLOSE'.
  call method of excel 'QUIT'.

  free object sheet.
  free object workbook.
  free object excel.
endform.


*---------------------------------------------------------------------*
*       FORM sheet                                                    *
*---------------------------------------------------------------------*
*       ........                                                      *
*---------------------------------------------------------------------*
form sheet.

  do p_wsheet times.
    unpack sy-index to sheetname+5(2).

    if sy-index gt 1.
      CALL METHOD  OF EXCEL 'WORKSHEETS' = sheet.
      call method of sheet  'ADD'.
      free object sheet.
    endif.
    scnt = sy-index.
    call method  of excel     'WORKSHEETS' = SHEET EXPORTING #1 = scnt.
    call method  of sheet     'ACTIVATE'.
    SET PROPERTY OF SHEET     'NAME'       = sheetname.
    free object sheet.   "OK


    perform fill_sheet.
*
    CALL METHOD OF EXCEL 'Columns' = COLUMN.
    CALL METHOD OF COLUMN 'Autofit'.
    free object COLUMN.
*
*    call method of sheet 'BUTTON' = button.
*    call method of button 'ADD'.
*    set property of button 'fmButtonStyle' = 0.
*      exporting #1 = '96.75' #2 = '372' #3 = '123.75' #4 = '12'.
*    set property of button 'Characters' = 'ButtonTest'.

    free object button.
    free object font.
    free object int.
    free object cell.
    free object: cell1.
    free object range.
    free object borders.
    free object: column, row.
  enddo.

  free object font.
  free object int.
  free object cell.
  free object cell1.
  free object range.
  free object borders.
  free object column.
  free object row.
  free object sheet.
endform.


*---------------------------------------------------------------------*
*       FORM border                                                   *
*---------------------------------------------------------------------*
*       ........                                                      *
*---------------------------------------------------------------------*
*  -->  we                                                            *
*---------------------------------------------------------------------*
form border using we.
*left
  call method of CELL 'BORDERS' = BORDERS exporting #1 = '1'.
  set property of borders 'LineStyle' = '1'.
  set property of borders 'WEIGHT' = we.                    "4=max
  free object borders.
* right
  call method of CELL 'BORDERS' = BORDERS exporting #1 = '2'.
  set property of borders 'LineStyle' = '2'.
  set property of borders 'WEIGHT' = we.
  free object borders.
* top
  call method of CELL 'BORDERS' = BORDERS exporting #1 = '3'.
  set property of borders 'LineStyle' = '3'.
  set property of borders 'WEIGHT' = we.
  free object borders.
* bottom
  call method of CELL 'BORDERS' = BORDERS exporting #1 = '4'.
  set property of borders 'LineStyle' = '4'.
  set property of borders 'WEIGHT' = we.
*    set property of borders 'ColorIndex' = 'xlAutomatic'.
  free object borders.
endform.

*---------------------------------------------------------------------*
*       FORM border2                                                  *
*---------------------------------------------------------------------*
*       ........                                                      *
*---------------------------------------------------------------------*
*  -->  we                                                            *
*---------------------------------------------------------------------*
form border2 using we.
*left
  call method of CELL 'BORDERS' = BORDERS exporting #1 = '1'.
  set property of borders 'LineStyle' = '5'.
  set property of borders 'WEIGHT' = we.                    "4=max
  free object borders.
* right
  call method of CELL 'BORDERS' = BORDERS exporting #1 = '2'.
  set property of borders 'LineStyle' = '6'.
  set property of borders 'WEIGHT' = we.
  free object borders.
* top
  call method of CELL 'BORDERS' = BORDERS exporting #1 = '3'.
  set property of borders 'LineStyle' = '7'.
  set property of borders 'WEIGHT' = we.
  free object borders.
* bottom
  call method of CELL 'BORDERS' = BORDERS exporting #1 = '4'.
  set property of borders 'LineStyle' = '8'.
  set property of borders 'WEIGHT' = we.
*    set property of borders 'ColorIndex' = 'xlAutomatic'.
  free object borders.
endform.

*---------------------------------------------------------------------*
*       FORM border3                                                  *
*---------------------------------------------------------------------*
*       ........                                                      *
*---------------------------------------------------------------------*
*  -->  we                                                            *
*---------------------------------------------------------------------*
form border3 using we.
*left
  call method of CELL 'BORDERS' = BORDERS exporting #1 = '1'.
  set property of borders 'LineStyle' = '9'.
  set property of borders 'WEIGHT' = we.                    "4=max
  free object borders.
* right
  call method of CELL 'BORDERS' = BORDERS exporting #1 = '2'.
  set property of borders 'LineStyle' = '10'.
  set property of borders 'WEIGHT' = we.
  free object borders.
* top
  call method of CELL 'BORDERS' = BORDERS exporting #1 = '3'.
  set property of borders 'LineStyle' = '11'.
  set property of borders 'WEIGHT' = we.
  free object borders.
* bottom
  call method of CELL 'BORDERS' = BORDERS exporting #1 = '4'.
  set property of borders 'LineStyle' = '12'.
  set property of borders 'WEIGHT' = we.
*    set property of borders 'ColorIndex' = 'xlAutomatic'.
  free object borders.
endform.


*---------------------------------------------------------------------*
*       FORM fill_cell                                                *
*---------------------------------------------------------------------*
*       ........                                                      *
*---------------------------------------------------------------------*
*  -->  color                                                         *
*  -->  pattern                                                       *
*---------------------------------------------------------------------*
form fill_cell using color pattern.
  call method of cell 'INTERIOR' = int.
  set property of int 'ColorIndex' = color.
  set property of int 'Pattern' = pattern.
  free object int.
endform.

*---------------------------------------------------------------------*
*       FORM font                                                     *
*---------------------------------------------------------------------*
*       ........                                                      *
*---------------------------------------------------------------------*
*  -->  bold                                                          *
*  -->  size                                                          *
*---------------------------------------------------------------------*
form font using bold size.
  call method  of CELL 'FONT' = font.
  set property of font 'BOLD' = bold.
  set property of font 'SIZE' = size.
  free object font.
endform.


*---------------------------------------------------------------------*
*       FORM fill_sheet                                               *
*---------------------------------------------------------------------*
*       ........                                                      *
*---------------------------------------------------------------------*
form fill_sheet.
  CALL METHOD  OF EXCEL   'RANGE' = CELL EXPORTING #1 = 'A1'.
  perform font          using 1 '14'.
  SET PROPERTY OF CELL    'VALUE' = 'Counter'.
  perform fill_cell     using '15' '1'.
  perform border        using '2'.
  free object cell.

  val = 'Workbook-Count'.
  move wb to val+16.
  CALL METHOD  OF EXCEL   'RANGE' = CELL EXPORTING #1 = 'B1'.
  SET PROPERTY OF CELL    'VALUE' = val.
  perform fill_cell using '14' '1'.
  perform border using '4'.
  free object cell.

  val = 'Sheet-Count'.
  unpack sy-index to val+12.
  CALL METHOD  OF EXCEL   'RANGE' = CELL EXPORTING #1 = 'C1'.
  SET PROPERTY OF CELL    'VALUE' = val.
  perform fill_cell using '12' '1'.
  perform border using '4'.
  free object cell.

  CALL METHOD  OF EXCEL   'RANGE' = CELL EXPORTING #1 = 'E3'.
  perform border        using '1'.
  free object cell.
  CALL METHOD  OF EXCEL   'RANGE' = CELL EXPORTING #1 = 'E5'.
  perform border        using '2'.
  free object cell.
  CALL METHOD  OF EXCEL   'RANGE' = CELL EXPORTING #1 = 'E7'.
  perform border        using '3'.
  free object cell.
  CALL METHOD  OF EXCEL   'RANGE' = CELL EXPORTING #1 = 'E9'.
  perform border        using '4'.
  free object cell.
  CALL METHOD  OF EXCEL   'RANGE' = CELL EXPORTING #1 = 'F3'.
  perform border2       using '1'.
  free object cell.
  CALL METHOD  OF EXCEL   'RANGE' = CELL EXPORTING #1 = 'F5'.
  perform border2       using '2'.
  free object cell.
  CALL METHOD  OF EXCEL   'RANGE' = CELL EXPORTING #1 = 'F7'.
  perform border2       using '3'.
  free object cell.
  CALL METHOD  OF EXCEL   'RANGE' = CELL EXPORTING #1 = 'F9'.
  perform border2       using '4'.
  free object cell.
  CALL METHOD  OF EXCEL   'RANGE' = CELL EXPORTING #1 = 'G3'.
  perform border3       using '1'.
  free object cell.
  CALL METHOD  OF EXCEL   'RANGE' = CELL EXPORTING #1 = 'G5'.
  perform border3       using '2'.
  free object cell.
  CALL METHOD  OF EXCEL   'RANGE' = CELL EXPORTING #1 = 'G7'.
  perform border3       using '3'.
  free object cell.
  CALL METHOD  OF EXCEL   'RANGE' = CELL EXPORTING #1 = 'G9'.
  perform border3       using '4'.
  free object cell.

  val = 'ROW-Count'.

  do 19 times.
    c_row = sy-index + 1.
    unpack c_row to val+12(4).
    CALL METHOD  OF excel 'CELLS' = CELL1 EXPORTING #1 = c_row #2 = 2.
    SET PROPERTY OF CELL1    'VALUE' = val.
    free object cell1.
    CALL METHOD  OF excel 'CELLS' = CELL1 EXPORTING #1 = c_row #2 = 4.
    SET PROPERTY OF CELL1    'VALUE' = val.
    free object cell1.
  enddo.



endform.

*----------------------------------------------------------------------*
* You find SAP OLE programs under development Class 'SOLE'             *
*                                                                      *
*  MSTAPPL  Table Maintenance APPL                                     *
*  RSOLEDOC Document list                                              *
*  RSOLEIN0 OLE Load Type Information                                  *
*  RSOLEINT Type Info Loaded                                           *
*  RSOLETI0 OLE Object Browser                                         *
*  RSOLETI1 OLE Object Browser                                         *
*  RSOLETI2 OLE Object Browser                                         *
*  RSOLETI3 F4 Help For OLE Objects                                    *
*  RSOLETT1 OLE 2.0 Automation Demo Program                            *
*                                                                      *
* Transactions:                                                        *
* SOLE                                                                 *
* SOLO  - List of OLE applcations with loaded type info                *
*                                                                      *
*                                                                      *
* You will find the decription of possible objects and methods in the  *
* windows help file for excel.                                         *
*----------------------------------------------------------------------*

Read only

Go to solution
Former Member

‎2008 Feb 18 6:09 AM

0 Likes
477

>Yes, you can open excel sheet within SAP. The following steps will help you achieve the same.

>>1. Click (Change layout...). The Change layout box will open.

>>2. Click the View tab.

>>3. Choose the Excel option.

>>4. Click on sap_om.xls in the Template list that displays on

the right side of the box.

>>5. Click (Transfer).

>>6. Microsoft Excel will open in the window where the report

data was displayed. You can now use all of the options within

Microsoft Excel and SAP to format and manipulate the data as

necessary.

>>7. To save the file, follow the Excel menu path File > Save

Copy As.

>>8. To close Excel, click (Change layout...), click the View

tab, choose the Grid option, and click

(Transfer).