Skip to Content
0
Mar 11, 2016 at 07:01 AM

Protect selected range of rows & Columns in excell using OLE

1040 Views

Hi,

I want to protect only selected range of rows & columns in excell sheet using OLE Concept. I tried using Locked and Protect properties but whole sheet is getting protected. Please find my below code.

TYPE-POOLS: soi,ole2.

DATA: lo_application TYPE ole2_object,

lo_workbook TYPE ole2_object,

lo_workbooks TYPE ole2_object,

lo_range TYPE ole2_object,

lo_worksheet TYPE ole2_object,

lo_worksheets TYPE ole2_object,

lo_column TYPE ole2_object,

lo_row TYPE ole2_object,

lo_cell TYPE ole2_object,

lo_font TYPE ole2_object.

DATA: lo_cellstart TYPE ole2_object,

lo_cellend TYPE ole2_object,

lo_selection TYPE ole2_object,

lo_validation TYPE ole2_object.

Data: gh_zl TYPE OLE2_OBJECT,

gh_f TYPE OLE2_OBJECT.

DATA: lv_selected_folder TYPE string,

lv_complete_path TYPE char256,

lv_titulo TYPE string.

CALL METHOD cl_gui_frontend_services=>directory_browse

EXPORTING

window_title = lv_titulo

initial_folder = 'C:\'

CHANGING

selected_folder = lv_selected_folder

EXCEPTIONS

cntl_error = 1

error_no_gui = 2

OTHERS = 3.

CHECK NOT lv_selected_folder IS INITIAL.

CREATE OBJECT lo_application 'Excel.Application'.

CALL METHOD OF lo_application 'Workbooks' = lo_workbooks.

CALL METHOD OF lo_workbooks 'Add' = lo_workbook.

SET PROPERTY OF lo_application 'Visible' = 0.

GET PROPERTY OF lo_application 'ACTIVESHEET' = lo_worksheet.

*Fill data

PERFORM FILL_CELL USING 1 1 1 'TEST1'(001).

PERFORM FILL_CELL USING 1 2 1 'TEST2'(001).

PERFORM FILL_CELL USING 2 1 1 'TEST3'(001).

PERFORM FILL_CELL USING 2 2 1 'TEST4'(001).


*Select range of rows columns

CALL METHOD OF lo_worksheet 'Cells' = lo_cellstart

EXPORTING

#1 = 1

#2 = 1.

CALL METHOD OF lo_worksheet 'Cells' = lo_cellend

EXPORTING

#1 = 1

#2 = 2.

CALL METHOD OF lo_worksheet 'RANGE' = lo_range

EXPORTING

#1 = lo_cellstart

#2 = lo_cellend.

CALL METHOD OF lo_range 'select'.

CALL METHOD OF lo_application 'selection' = lo_selection.

SET PROPERTY OF lo_selection 'Locked' = 1.

CALL METHOD OF lo_worksheet 'Protect'

EXPORTING

#1 = 0

#2 = 0.

CONCATENATE lv_selected_folder '\Test' INTO lv_complete_path.

CALL METHOD OF lo_workbook 'SaveAs'

EXPORTING

#1 = lv_complete_path.

IF sy-subrc EQ 0.

MESSAGE 'File downloaded successfully' TYPE 'S'.

ELSE.

MESSAGE 'Error downloading the file' TYPE 'E'.

ENDIF.

CALL METHOD OF lo_application 'QUIT'.

FREE OBJECT lo_worksheet.

FREE OBJECT lo_workbook.

FREE OBJECT lo_application.

FORM fill_cell USING i j bold val. "For filling data in excel.

CALL METHOD OF

lo_application

'CELLS' = gh_zl

NO

FLUSH

EXPORTING

#1 = i

#2 = j. "Transferring data to row = i and column = j.

"For handling automation error.

PERFORM ERR_HAND.

SET PROPERTY OF gh_zl 'VALUE' = val no flush.

PERFORM ERR_HAND.

GET PROPERTY OF gh_zl 'FONT' = gh_f no flush.

PERFORM ERR_HAND.

SET PROPERTY OF gh_zl 'NumberFormat' = '@'.

PERFORM ERR_HAND.

SET PROPERTY OF gh_f 'BOLD' = bold no flush.

PERFORM ERR_HAND.

ENDFORM.

FORM ERR_HAND.

*return message in case of error.

IF SY-SUBRC <> 0.

WRITE: / 'OLE ERROR: RETURN CODE ='(I10), SY-SUBRC.

STOP.

ENDIF.

ENDFORM.


Thanks in advance.