Excel and ABAP: protect sheet

[Former Member]

The method 'Protect' from the spreadsheet interface will protect the worksheet in Excel with a password. However, password is not one of the parameters of the method, hence SAP provides a "secret" password somewhere.

This would mean I would have to use the method 'Protect' again to unprotect the worksheet. However I would like to be able to change the worksheet using Excel.

Does anyone knows this password or is there another way to provide the password for protecting the worksheet?

[Former Member]

There is a alternate method (using OLE) that fits your requirement.

Download the file contents to NT / Local drive... Apply OLE to the data file (Use EXCEL Macro to record the Excel sheet actions (like BDC Recording) and you have to implement all the method statments recorded by EXCEL Macro recording in ABAP..With this, You can set your own password (Static or Dynamically determined by program logic)

Ig you have any further Q's , Send me a mail..I will send you a sample code..

[Former Member]

Hello John,

I have solved a similar problem. But i have used an other approach.

1.) Fetch the Data from SAP via VBA and RFC from Excel

2.) Afterwards you can send the Excel (with protected ranges) to your partner using build in functionality.

3.) If you get back the sheet you can send the result to SAP (using f.x. inplace EXCEL).

This solution is a little bit tricky but works fine.

If you want, i can sent you some sample coding.

BR

Michael

[Former Member]

Hi Michael,

I have a similar problem. We are trying to upload an excel file to SAP. The client wants to run a macro in excel that will call RFC and update SAP. We have so far done the macro in excel and remote-enabled FM but when we call the RFC in the macro we get a RFC_SYSTEM_CONNECTION error due to timeout. How can we address this?

NOTE: without data being passed, the connection is established successfully from the EXCEL macro

Will appreciate your help. Thanks

best regards,

John

[Former Member]

Hello John,

It seems, that your problem is a result of wrong

data-preparation (Tables-Parameter within RFC)

BR

Michael

[former_member93896]

Hello John,

the protect method locks only the cells that have the "locked" property turned on. Since this is the default the complete sheet will be locked unless you turn off this property first. For that reason you have the protect_range method. Use it to unlock the cells that you want to work with in Excel. Then call the protect method.

Regards,

Marc

SAP NetWeaver RIG, US BI

[Former Member]

Thanks for your quick answer Marc. However, this still doesn't solve my problem.

I would like to create an Excel sheet using ABAP (and protect some ranges in the sheet). The sheet will be sent to suppliers to fill in some data and will be returned. Then I would like to process the sheet just using Excel and for that reason I would like to be able to unprotect the sheet. To unprotect the range/sheet I have to supply a password... Since "password" is not an parameter of either the method Protect/Protect_range this password is supplied somewhere else.

Kind regards,

John.