Write Code in Excel thru SAP

[Former Member]

i have made report in SAP which directly opens in Excel,

i have protected the sheet but my requirement is that user shud not be able to cut Copy and paste the data from that sheet while protecting he is unable to do any modification my reqt is that he shud not be allowed to cut and copy the data also.

i have written the following code

REPORT  z_test.



INCLUDE ole2incl.

* handles for OLE objects
DATA: h_excel   TYPE ole2_object,        " Excel object
      h_mapl    TYPE ole2_object,        " list of workbooks
      h_map     TYPE ole2_object,        " workbook
      h_cell    TYPE ole2_object,        " cell
      h_f       TYPE ole2_object,        " font
      h_col     TYPE ole2_object,
      h_row     TYPE ole2_object,
      h_int     TYPE ole2_object,
      h_auto    TYPE ole2_object,
      h_select  TYPE ole2_object,
      h_range   TYPE ole2_object,
      h_merge   TYPE ole2_object,
      h_columns TYPE ole2_object,
      h_rows    TYPE ole2_object,
      h_borders TYPE ole2_object,
      h_active  TYPE ole2_object,
      row       TYPE i,
      col       TYPE i,
      v_prog(70),
      v_range1(10),
      v_range2(10),
      v_r1(10),
      v_r2(10).

*----------------------------------------------------------------------
*


START-OF-SELECTION .

END-OF-SELECTION .
  PERFORM f_start_excel .
  PERFORM f_display_hdr .
  PERFORM f_stop_excel .
*


*&---------------------------------------------------------------------
*
*&      Form  f_start_excel
*&---------------------------------------------------------------------
*
FORM f_start_excel .
* start Excel
  CREATE OBJECT h_excel 'EXCEL.APPLICATION'.
  SET PROPERTY OF h_excel  'Visible' = 1.

* get list of workbooks, initially empty
  CALL METHOD OF h_excel 'Workbooks' = h_mapl.
  PERFORM err_hdl.
* add a new workbook
  CALL METHOD OF h_mapl 'Add' = h_map.
  PERFORM err_hdl.
ENDFORM.                    " f_start_excel
*&---------------------------------------------------------------------
*&      Form  f_stop_excel
*&---------------------------------------------------------------------
*
FORM f_stop_excel .
  FREE OBJECT h_cell.

  CALL METHOD OF h_excel 'Cells' = h_cell .
  GET PROPERTY OF h_cell  'Select' = h_select.

  CALL METHOD OF h_cell 'Columns' = h_columns .
  CALL METHOD OF h_columns 'AutoFit' = h_auto .

  CALL METHOD OF h_cell 'Rows' = h_rows .
  CALL METHOD OF h_rows 'AutoFit' = h_auto .

  FREE OBJECT h_cell.

  CALL METHOD OF h_excel 'Cells' = h_cell
    EXPORTING
    #1 = 1
    #2 = 1.
  GET PROPERTY OF h_cell  'Select' = h_select.


  GET PROPERTY OF h_excel 'ActiveSheet' = h_active.

  CALL METHOD  OF h_active 'PROTECT'
    EXPORTING #1 = 'password'.


*
  SET PROPERTY OF h_excel  'Visible' = 1.

  FREE OBJECT h_excel.
  PERFORM err_hdl.
ENDFORM.                    " f_stop_excel
*&---------------------------------------------------------------------
*
*&      Form  ERR_HDL
*&---------------------------------------------------------------------
*
FORM err_hdl.
  IF sy-subrc <> 0.
    WRITE: / 'OLE Error :'(010), sy-subrc.
    STOP.
  ENDIF.
ENDFORM.                    " ERR_HDL


*&---------------------------------------------------------------------
*
*&      Form  f_display_hdr
*&---------------------------------------------------------------------
*
FORM f_display_hdr .
  row = 2 .
  PERFORM fill_cell USING row 3 'OHO SHEET'  1 35 1 1 0 16 0 1.
  PERFORM f_merge_cells USING 'A' 'E' -4108 .

  row = row + 2  .
  PERFORM fill_cell USING row 3 'Program Details'  1 43 1 1 0 12 0 1.
  PERFORM f_merge_cells USING 'A' 'E' -4108 .

ENDFORM.                    " f_display_hdr
*---------------------------------------------------------------------*
*       FORM FILL_CELL                                                *
*---------------------------------------------------------------------*
FORM fill_cell USING p_row    p_col    p_val  p_shrink p_bkclr p_pat
                     p_bold   p_italic p_size p_fclr   p_uline .

  CALL METHOD OF h_excel 'Cells' = h_cell
    EXPORTING
    #1 = p_row
    #2 = p_col.

  SET PROPERTY OF h_cell 'Value'       = p_val .
  SET PROPERTY OF h_cell 'ShrinkToFit' = p_shrink .

  GET PROPERTY OF h_cell 'Interior'   = h_int.
  SET PROPERTY OF h_int  'ColorIndex' = p_bkclr .
  SET PROPERTY OF h_int  'Pattern'    = p_pat.

  GET PROPERTY OF h_cell 'Font'    = h_f.
  SET PROPERTY OF h_f 'Bold'       = p_bold .
  SET PROPERTY OF h_f 'Italic'     = p_italic .
  SET PROPERTY OF h_f 'Size'       = p_size .
  SET PROPERTY OF h_f 'ColorIndex' = p_fclr .
  SET PROPERTY OF h_f 'Name'       = 'Arial' .
  SET PROPERTY OF h_f 'Underline'  = p_uline .
ENDFORM.                    "FILL_CELL
*&---------------------------------------------------------------------
*
*&      Form  f_merge_cells
*&---------------------------------------------------------------------
*
FORM f_merge_cells USING p_r1 p_r2 p_val.
  CLEAR : v_r1, v_r2, v_range1, v_range2 .

  v_range1 = row .
  v_range2 = row .
  SHIFT v_range1 LEFT DELETING LEADING ' ' .
  SHIFT v_range2 LEFT DELETING LEADING ' ' .

  CONCATENATE p_r1 v_range1 INTO v_r1 .
  CONCATENATE p_r2 v_range2 INTO v_r2 .

  CALL METHOD OF h_excel 'Range' = h_range
    EXPORTING
    #1 = v_r1
    #2 = v_r2.
  CALL METHOD OF h_range 'Select' = h_select .
  CALL METHOD OF h_range 'Merge' = h_merge .
  SET PROPERTY OF h_range 'HorizontalAlignment' = p_val.
ENDFORM.                    " f_merge_cells

i ave found the code for visual basi editor

Option Explicit

Sub ToggleCutCopyAndPaste(Allow As Boolean)

'Activate/deactivate cut, copy, paste and pastespecial menu items

Call EnableMenuItem(21, Allow) ' cut

Call EnableMenuItem(19, Allow) ' copy

Call EnableMenuItem(22, Allow) ' paste

Call EnableMenuItem(755, Allow) ' pastespecial

'Activate/deactivate drag and drop ability

Application.CellDragAndDrop = Allow

'Activate/deactivate cut, copy, paste and pastespecial shortcut keys

With Application

Select Case Allow

Case Is = False

.OnKey "^c", "CutCopyPasteDisabled"

.OnKey "^v", "CutCopyPasteDisabled"

.OnKey "^x", "CutCopyPasteDisabled"

.OnKey "+", "CutCopyPasteDisabled" .OnKey "^", "CutCopyPasteDisabled" Case Is = True .OnKey "^c" .OnKey "^v" .OnKey "^x" .OnKey "+"

.OnKey "^"

End Select

End With

End Sub

Sub EnableMenuItem(ctlId As Integer, Enabled As Boolean)

'Activate/Deactivate specific menu item

Dim cBar As CommandBar

Dim cBarCtrl As CommandBarControl

For Each cBar In Application.CommandBars

If cBar.Name <> "Clipboard" Then

Set cBarCtrl = cBar.FindControl(ID:=ctlId, recursive:=True)

If Not cBarCtrl Is Nothing Then cBarCtrl.Enabled = Enabled

End If

Next

End Sub

Sub CutCopyPasteDisabled()

'Inform user that the functions have been disabled

MsgBox "Sorry! Cutting, copying and pasting have been disabled in this workbook!"

End Sub

-

---

WORKBOOK EVENTS IN the this workbook -

---

Private Sub Workbook_Activate()

Call ToggleCutCopyAndPaste(False)

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Call ToggleCutCopyAndPaste(True)

End Sub

Private Sub Workbook_Deactivate()

Call ToggleCutCopyAndPaste(True)

End Sub

Private Sub Workbook_Open()

Call ToggleCutCopyAndPaste(False)

End Sub

but how to use this code in SAP

[Former Member]

Hi!

I don't think it can be done, because drag&drop, copy&paste is handled not by the excel, but the windows.

You might try out to set the cells property to read-only (this prevents their overwriting).

Regards

Tamá

[Former Member]

???

[Former Member]

?? any clues