topic Re: Protect Workbook in Application Development and Automation Discussions

Protect Workbook

Former Member — Mon, 10 Jan 2005 14:18:20 GMT

Does anyone know a method to protect an Excel workbook from Abap (the equivalent of 'Tools->Protection->Protect Workbook in Excel)?

Regards,

John.

Re: Protect Workbook

Former Member — Mon, 10 Jan 2005 14:21:43 GMT

SAP Help

Here you can find detailed information about excel integration.

http://help.sap.com/saphelp_47x200/helpdata/en/21/b53138e1ba11d2bdbe080009b4534c/frameset.htm

Message was edited by: Fuat Ulugay

Re: Protect Workbook

Former Member — Mon, 10 Jan 2005 14:27:52 GMT

Hi Fuat,

Thanks for the quick reaction, but that is not the solution. The 'Protect' method of the spreadsheet interface just protects cells from being overwritten. I want to protect the workbook from all kinds of editing to the structure (e.g. renaming sheets) as the 'Tools->Protection->Protect Workbook' does.

Regards,

John.

Re: Protect Workbook

Former Member — Mon, 10 Jan 2005 14:54:30 GMT

Hi John,

I just did a quick test in Excel and found the following VBA code protected the workbook:

ActiveWorkbook.Protect "mypass", True, False

Cheers,

Scott

Re: Protect Workbook

Former Member — Mon, 10 Jan 2005 15:10:51 GMT

Hi Scott,

We're getting there, but now the equivalent to be used in Abap. I tried already "CALL METHOD OF workbook 'PROTECT' " after opening the Excel workbook, but the returncode is set to '02'.

Regards,

John.

Re: Protect Workbook

ssimsekler — Mon, 10 Jan 2005 15:28:54 GMT

Hi John

Did you try passing the password parameter? And are you sure 'workbook' is the active workbook object?


CALL METHOD OF workbook 'Protect'
     EXPORTING #1 = <pass> .

*--Serdar

Re: Protect Workbook

Former Member — Mon, 10 Jan 2005 15:39:30 GMT

Hi Serdar,

I (think I) am sure. But for the record the piece of coding:

PROGRAM zjh.

TYPE-POOLS ole2.

DATA excel TYPE ole2_object.

DATA workbook TYPE ole2_object.

CREATE OBJECT excel 'Excel.Application'.

SET PROPERTY OF excel 'Visible' = 1.

CALL METHOD OF excel 'Workbooks' = workbook.

CALL METHOD OF workbook 'Open'

EXPORTING #1 = 'C:\TESTJOHN.XLS'.

CALL FUNCTION 'FLUSH'.

CALL METHOD OF workbook 'PROTECT'

EXPORTING #1 = 'password'.

With a break-point on the last statement the excel workbook is opened correctly; however executing the last statement will give returncode '02'.

Any suggestions?

John.

Re: Protect Workbook

Former Member — Mon, 10 Jan 2005 15:58:59 GMT

I haven't tested the logic properly, but from the code shown, it looks like variable "workbook" contains a reference to a Workbooks object rather than an instance of a Workbook object. I suspect return code 02 is returned because there is no Protect method for the Workbooks object???

Cheers,

Scott

Re: Protect Workbook

Former Member — Mon, 10 Jan 2005 16:07:23 GMT

Have done a test now, found the following variation of your code to work...


REPORT zz_bardens_excel .

TYPE-POOLS:
  ole2.

DATA:
  excel                      TYPE ole2_object,
  workbooks                  TYPE ole2_object,
  workbook                   TYPE ole2_object.

START-OF-SELECTION.

  CREATE OBJECT   excel 'Excel.Application'.
  SET PROPERTY OF excel 'Visible' = 1.

  GET PROPERTY OF excel 'Workbooks' = workbooks.

  CALL METHOD OF workbooks 'Add'.

  GET PROPERTY OF excel 'ActiveWorkbook' = workbook.

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

  WRITE sy-subrc.

Scott

Re: Protect Workbook

Former Member — Mon, 10 Jan 2005 16:16:41 GMT

Thanks Scott!

This works fine, 10 points for you! However, I'm still wondering if there is any decent documentation for all the methods you can call?

Regards,

John

Re: Protect Workbook

Former Member — Mon, 10 Jan 2005 16:44:50 GMT

Wow, that's my second lot of points today. I think I need a nap.

I just use the VBA Help file that comes with Microsoft Excel (if you chose the option to install it). Open up the VBA editor from the Macros menu in Excel and then go to its help menu.

Cheers,

Scott