topic OLE Excel: Protect Sheet & Allow to Insert Rows! in Application Development and Automation Discussions

OLE Excel: Protect Sheet & Allow to Insert Rows!

Former Member — Fri, 19 Oct 2007 19:59:05 GMT

Dear Folks

I am down loading into Excel via OLE Automation from CRM system. Excel sheet needs to be protected, able to do it and it is working fine.

But user wants to add rows(not columns) in the protected excel sheet. I went through some threads and it says to use the method 'AutoEdit'. But I don't know how to call this method & it's parameters to achieve my requirement. Is there any other method for this kind of requirement?

Please guide me and any valid inputs & step by(s) would be highly appreciated.

Re: OLE Excel: Protect Sheet & Allow to Insert Rows!

Former Member — Sun, 21 Oct 2007 09:40:50 GMT

Use OLE objects in ABAP like the "EXCEL via OLE Automation" document.

Try to create a macro in Excel with the same data and protect the cells. Then goto visual basic editor and you have the code in visual basic. At this moment you must translate the properties and methods in OLE (ABAP)

Re: OLE Excel: Protect Sheet & Allow to Insert Rows!

Former Member — Tue, 23 Oct 2007 20:45:24 GMT

Thanks Jorge Hernandez. It was really helpful.

I am having one more issue, How to call the method 'AutoFit' to make all selected columns to be fit. Please provide in detail.

Re: OLE Excel: Protect Sheet & Allow to Insert Rows!

Former Member — Thu, 25 Oct 2007 19:52:25 GMT

DATA: gs_excel TYPE ole2_object,

gs_wbooklist TYPE ole2_object,

gs_application TYPE ole2_object,

gs_wbook TYPE ole2_object,

gs_activesheet TYPE ole2_object,

gs_sheets TYPE ole2_object,

gs_newsheet TYPE ole2_object,

gs_cell1 TYPE ole2_object,

gs_cell2 TYPE ole2_object,

gs_cells TYPE ole2_object,

gs_range TYPE ole2_object,

g_o_dibujo TYPE ole2_object,

g_o_dibujo1 TYPE ole2_object,

g_o_borde TYPE ole2_object,

g_o_pagina TYPE ole2_object,

g_o_hbreaks TYPE ole2_object,

g_o_hbreak TYPE ole2_object,

gs_font TYPE ole2_object,

gs_interior TYPE ole2_object,

gs_columns TYPE ole2_object,

gs_charts TYPE ole2_object,

gs_chart TYPE ole2_object,

gs_charttitle TYPE ole2_object,

gs_charttitlechar TYPE ole2_object,

gs_chartobjects TYPE ole2_object.

DATA gv_sheet_name(20) TYPE c.

DATA gv_outer_index LIKE sy-index.

DATA gv_intex(2) TYPE c.

DATA gv_line_cntr TYPE i. "line counter

DATA gv_linno TYPE i. "line number

DATA gv_colno TYPE i. "column number

DATA gv_value TYPE i. "data

gv_sheet_name = 'RPT'.

CREATE OBJECT gs_excel 'EXCEL.APPLICATION'.

SET PROPERTY OF gs_excel 'Visible' = 1.

GET PROPERTY OF gs_excel 'Workbooks' = gs_wbooklist.

GET PROPERTY OF gs_wbooklist 'Application' = gs_application.

SET PROPERTY OF gs_application 'SheetsInNewWorkbook' = 1.

CALL METHOD OF gs_wbooklist 'Add' = gs_wbook.

GET PROPERTY OF gs_application 'ActiveSheet' = gs_activesheet.

SET PROPERTY OF gs_activesheet 'Name' = gv_sheet_name.

CALL METHOD OF gs_activesheet 'Columns' = gs_columns.

CALL METHOD OF gs_columns 'Autofit' NO FLUSH.