No variable popup in Analyzer

[Former Member]

Hi,

I have created a workbook with several tabs (each tabs have the same query but with diferent 'tasks') but I have problem : there is no popup in variable screen when I open my workbook.

In fact, there is no problem when I open query designer, I don't have errors and I can choose my 'project' but in analyzer, my workbook open directly without variable screen for 'project'. However, I can 'change variables values' when this workbook is opened.

On the other side, I have created a macro for excel to protect the sheet but it doesn't execute (I don't know why... I have to do it manually but it is an other problem). I have realized that when my workbook is protected, I can't click on 'change variables values' button, whereas when my workbook is unprotected, I can click on this button !

Thanks in advance for answers

V.K

[Former Member · Accepted solution]

Muralidhar, I have to use macro because I am not protect all tabs : 3 columns are unprotected for users. They can input data ; some columns contains calculation and the others restore data.

[former_member188325 · Accepted solution]

from SAP help:

Workbook Settings ---> Exits

On the Exits tab page, you can choose whether a macro can be called during the refresh. You can choose any macro name. Note that the macro is only called when the workbook is opened if the Refresh Workbook on Open indicator is set on the General tab page.

The signature of the macro must be as follows: Sub <MACRO_NAME>(ParamArray varname())

<MACRO_NAME> is a place holder for the name of the macro, which can be any text of your choice, for example, RefreshMacro.

The name of the data provider is in varname(0).

The range is contained in varname(1).

Example:

Sub RefreshMacro(ParamArray varname())

Dim lData_Provider As String

lData_Provider = varname(0)

Set lRange = varname(1)

MsgBox lData_Provider

End Sub

[Former Member · Accepted solution]

As krishna said,

Again i am repeating the same one.

Goto workbook settings and check Refresh Workbook on Open and try.

Regards,

[Former Member]

Thanks Muralidhar. You have solved my problem !

[Former Member]

I am working with BI7.

I have an other workbook with the same queries and the same macro but I can 'change variable values' in spite of this workbook is protected..

[former_member188325]

In BI7.0, you can Protect the Workbook using 'workbook settings' of Design mode.Actiavte 'Password-protect workbook' and enter the pssword. You dont need a macro for that. Then you should be able to use Analysis toolbar functions without any issues.Password is required to use any of the Design toolbar functions.

[Former Member]

Hi,

Goto workbook settings and check Refresh Workbook on Open and try.

Hope it helps!

Krishna

[former_member188325]

In case of BW3.5: check 'refresh while opening workbook' to get the variable screen while opening it.

and yes you can not use change variable values if workbook is protected.

Include the following code in your module

-

---

Public Sub Refresh()

Run "SAPBEX.xla!SAPBEXrefresh", (True)

End Sub

Sub SAPBEXonRefresh(queryID As String, resultArea As Range)

Unprotect the sheet using vba

<include your code here>

Protect teh sheet

End Sub

-

---

Create a button in the worksheet and assign teh macro 'Refresh' to it.So,it is still possible to refresh the workbook with pushbutton without using BEx toolbar.

If you are working with BI7 Analyzer,concet is totally different.