Automatic protection of an Excel WorkBook with Analysis

[Former Member]

Hi everyone,

I'm using Analysis for MS Excel connected to BW, and the idea is to allow the users to update all the queries included in the Workbook and that inmediately after that the workbook gets automatically protected, so that the data format,etc. can't be modified.

I dont know if these could be done with a Macro, or if it is even possible. Any help would be appreciated.

Leonardo.

[shruti_srivastava3]

Hi All ,

We have a similar requirement where we need to maintain the report formatting and restrict users to make any changes to actual report. But When I try to password protect the sheet , it does not allow me to expand/collapse the hierarchy nodes. Is there a way that we can protect the worksheet but the drilling capabilities are enabled. Please suggest

[maagp]

Hi Leonardo,

You could use the Workbook_SAP_Initialize callback of Analysis Office or  a button to trigger a refresh of the datasource(s) and afterwards protect the workbook or the sheet(s).

Here's an simple example using Workbook_SAP_Initialize. When opening the workbook it refreshs the datasource DS_1 and protects the active sheet afterwards. It does not exactly what you want but you could adapt it to your needs.

------------------------------------------------------------------------------

' For information about AO callbacks / functions, see User Guide on http://help.sap.com/boaa

' Called after AO initialization

Public Sub Workbook_SAP_Initialize()

    Dim lResult As Long

    lResult = Application.Run("SAPExecuteCommand", "Refresh", "DS_1")

    If lResult = 1 Then

         ProtectActiveSheet

    End If

End Sub

' this method protects the active sheet, using the password "password"

Public Sub ProtectActiveSheet()

    ActiveSheet.Protect "password", True, True

End Sub

' this method would unprotect the active sheet

Public Sub UnprotectActiveSheet()

    ActiveSheet.Unprotect "password"

End Sub

-------------------------------------------------------------------------

Best regards,

Patrik

[Former Member]

Hi Patrik,

I have tried your coding, unfortunately its protecting all crosstabs on all excel worksheets, even the BO A Ribbons.

Might be I am doing something wrong.

Ralf

[Former Member]

Small correction, the BO A functions are disabled for all crosstabs.

[maagp]

Hi Ralf,

You're right, Analysis Office currently disables all commands when one of the Workbook sheets is protected.

There's a new submission on the Ideas Place about changing this behaviour. Please promote this idea if you would like to raise it's priority: "Ability to Lock an Excel Workbook without Disabling BO Analysis Functionality"

Sorry for the late reply & best regards,

Patrik

[Former Member]

Hi Patrick,

thanks for the feedback we have placed this idea.

And still we do not accept to have this only placed as an idea.

There are too many standard excel functionalities which are not working anymore using

BO A, or vice versa BO a is not working as expected when using standard excel functions.

We need to understand the business processes behind. Here we are not talking about one workbook

with with one DS. Most of teh finance users do have very complex workbooks. And all for a sudden

that cannot use anymore standard excel procedures, only as of having BO A as a frontend.

For all these items we have raised sap messages.

Ralf

[alex_zetune3]

The trick is

1. to unprotect certain cells,

2. Protect the sheet (no password required)

3. before calling the SAP functions, unprotect the sheet

4. Call the SAP function

5. Protect the sheet

Code to protect and uprotect from Patrik Maag should work