cancel
Showing results for 
Search instead for 
Did you mean: 

Offline mode setting causing incorrect named ranges to appear

0 Kudos

Hi guys,

I have a report that we need to turn offline to send to non-BPC users. We have a number of sheets on said report that don't contain EPM reports and so I need to make use of the "Protect Only Sheets Containing EPM Reports" user option - we need this because the non-EPM sheets have dropdowns that can change and should allow users to type on them.

Manually selecting the user option works just fine. When I turn the workbook offline, the EPM sheets are correctly in offline mode and protected and the non-EPM sheets are unprotected.


However... I am a super user here, but I need this report to be accessible to all end users from the company folder. So I have used an API, referring to the EPM Plug-in user guide, to try to force this option to update on open. I have used

With OWSEPM .SetUserOption "OfflineUnprotected", True

The VBA itself works just fine. However, it seems to also force the second tickbox in the image above, so after opening it looks like:

So my first question is, does anyone know if there is a way to force the 1st tickbox above without the 2nd one through VBA? I can't find anything in the user guide.

I had presumed it would be OK to have both boxes ticked and proceeded. However, when turning the report offline, it duplicates any named ranges I've created and gives them a really bizarre range. E.g. see below. The named ranges not highlighted are the ones I've created, the named ranges that are highlighted is what the system creates when I turn the report offline:

So it seems that the "Static Offline Without Protection" creates a new sheet? (the iuOtK+Lo... one).

When I download the offline mode file and send it to users, my dropdowns are referring to these named ranges, yet it seems Excel "prioritises" the highlighted yellow ones. But it can't find a sheet called this and so throws #NAME errors.

So my second point is, has anyone come across this error before? Or just generally can suggest a fix?

FYI we're NW 10.1 running on Analysis for office 2.6.100.78647


Thanks!

Joe



Accepted Solutions (1)

Accepted Solutions (1)

former_member186338
Active Contributor
0 Kudos

"I have used

With OWSEPM .SetUserOption "OfflineUnprotected", True"

Is related to the checkbox: "Static Offline Without Protection", it will indirectly set "Protect Only Sheets Containing EPM Reports"

Unfortunately there is no defined property string to set "Protect Only Sheets Containing EPM Reports" directly.

"Static Offline Without Protection" is a special way of protection when duplicated sheets are created (look on veryHidden sheets in VBA editor).

0 Kudos

I had suspected as much (i.e. no way to set the first option only) - just wanted to double check on here.

So I'm assuming there is no suitable workaround at this time! Hopefully SAP can change this in future. I'll have to tell the users to manually set that option each time, which is frustrating.

Thanks for your help.

Answers (2)

Answers (2)

former_member186338
Active Contributor
0 Kudos

In VBA open file C:\Users\UserName\AppData\Local\EPMOfficeClient\EPMXLClientPreference.XML , read it into string variable

and replace: <OfflineOnlyEPMReportsProtected>false</OfflineOnlyEPMReportsProtected>

with <OfflineOnlyEPMReportsProtected>true</OfflineOnlyEPMReportsProtected>

Save file.

former_member186338
Active Contributor
0 Kudos

Another option is to play with configuration file located:

C:\Users\UserName\AppData\Local\EPMOfficeClient\EPMXLClientPreference.XML

Look on the line:

<OfflineOnlyEPMReportsProtected>true</OfflineOnlyEPMReportsProtected>

0 Kudos

Yeah I had thought of this also. Was wary of going this route as users can obviously change these settings away from the config file - but in this case it may be the best approach!

Thanks

Joe