Analysis: How to open a query in display mode and switching to input ready via macro?

[DanielElwart]

Hi everybody,

for our customer we created a simple planning sheet with Analysis in MS Office 2010.

Everythink works fine but we do not want the workbook to open the query in change mode.

The user should be able to switch to change mode using a button on the sheet.

Unfortunately we need to set on the Design Panel the mark to open the query in the input ready mode.

So first user blocks all others. Settings special filters or authoriziation is not an option here.

Opening the workbook only in display mode and trying to switch to change mode using

Application.Run("SAPExecuteCommand", "PlanDataToChangeMode")

does not work.

I get a result 14, "Command PlanDataToChangeMode is not enabled".

How can I enable the command, without using the Design Panel?

Best regards

Daniel

[Former Member · Accepted solution]

Hi Daniel,

it will work if you call the Command on "Workbook_SAP_Initialize()". Please make sure that you refresh your datasources before running that command.

Coding:

Public Sub Workbook_SAP_Initialize()

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

     lResult = Application.Run("SAPExecuteCommand", "PlanDataToDisplayMode")

End Sub

Best regards,

Stefan

[DanielElwart · Accepted solution]

Hi Stefan,

I tried that in a little dummy report on our test system.

Worked good, I will implement it at our customer within the next couple of weeks.

I think the important thing is the refresh.

Many thanks

Daniel

[Former Member · Accepted solution]

Hi Stephan and folks.

I have the same requirement to open the workbook on Display mode, but I do not want to refresh the data at opening.

I have several tabs in excel with one IP ready query and several reports for support.

Most of the times, my users will go there to check the reports and not to enter data. To avoid unnecessary locking, I want to enter in display mode, and for performance reasons, I do not want a refresh every time that they open the file (they saved the files with data in a shared drive).

Let me know if you have any ideas...

Thanks in advance

Rodrigo Silva

[DanielElwart · Accepted solution]

Hi Rodrigo,

you could try to refresh only the input ready query if you want.

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

Best regards

Daniel

[Former Member · Accepted solution]

Thanks Daniel,

I tested the code in conjunction with the commands to activate analysis automatically and they worked fine.

Appreciate your help.

Rodrigo

This is my code:

_______________________________________________________________________

Option Explicit

Private Sub Workbook_Open()

    Call EnableAnalysisOffice

End Sub

_______________________________________________________________________

Private Sub EnableAnalysisOffice()

  Dim addin As COMAddIn

   For Each addin In Application.COMAddIns

        If addin.progID = "SBOP.AdvancedAnalysis.Addin.1" Then

            If addin.Connect = False Then addin.Connect = True

        End If

    Next

End Sub

_______________________________________________________________________

Public Sub Workbook_SAP_Initialize()

Dim lResult As Long

'***Refresh only the Input template

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

'*** Set Input template to Display Mode - to avoid data locks.

lResult = Application.Run("SAPExecuteCommand", "PlanDataToDisplayMode")

End Sub

_______________________________________________________________________

[Former Member · Accepted solution]

I have another situation now,

I have 2 planning queries on the same workbook and I would like to set one to Plan mode and another to Display mode.

When I use the standard button (Change / Display) on the planning ribbon, it apply to both data sources.

I tried the code but did not work...

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

Public Sub Workbook_SAP_Initialize()

Dim lResult As Integer

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

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

lResult = Application.Run("SAPExecuteCommand", "PlanDataToChangeMode", "DS_2")

End Sub

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

any ideas?

Thank you

Rodrigo

[DanielElwart · Accepted solution]

Hi Rodrigo,

as far as I know you can only switch between display and input for all queries in the workbook.

We had a similar discussion at our customer but till now we can only switch the whole workbook.

It is the same for saving the data.

Best regards

Daniel

[Former Member]

I am confused.

If you want topen the Query in "read mode" you could use the Query Setting for that.

For that open the QueryDesigner and go to Query properties

Change the setting there and everything should be fine...

Regards

[DanielElwart]

Hello Thorsten,

If I checked this in the query, I was not able to get from Analysis side back to plan mode.

Maybe that was due to the Version of Analysis then, I will try it out on monday if this works now.

Best regards

Daniel

[DanielElwart]

Hi Thorsten,

If I uncheck this in the query designer the query opens in display mode for sure.

But If I than want to switch to change mode I have to open the Design Panel and check there again to go back to change mode. And this what I want do avoid.

I would like to create a button in the workbook to do that, but unfortunately using

Application.Run("SAPExecuteCommand", "PlanDataToChangeMode")

does not work than.

Best regards

Daniel

[Former Member]

Hi Daniel,

I'm not sure if I really understand your issue, since I don't have a planning query to test...

But maybe some more questions will lead to an answer?!

Is it possible to open the query automatically in plan mode (I guess this is done via the check box in the components tab) and then dhange it to Display Mode via macro (PlanDataToDisplayMode)?

If this works, maybe it is possible to change to Plan mode via macro as second statement?

Or, do you need to do a Refresh of the datasource first, like it is necessary for any VBA functionality when setting a filter value or an input variable?

Regards,

Martin

[DanielElwart]

Hi Marin,

Is it possible to open the query automatically in plan mode -> yes

and then change it to Display Mode via macro -> yes and no

yes for, it is possible, if I start the macro manual

no for, I do not find the point of time when to start this macro automatically.

I tried this via macro on workbook open, that does not work.

I tried this in the Callback "after redisplay", that does not work (cause the redisplay callback is still running).

Any idea, when to start the macro at best? It should run automatically after opening.

A possible woraround is inserting a button that the user has to press at first, but that is not very user friendly.

Best regards Daniel

[Former Member]

Hi Daniel,

I'm wondering why the macro is not working on "workbook open"?

Is you Add-in not yet active?

I use the respective coding to start the add-in directly, so afterwards I can also trigger refresh etc. commands...

Try:

Private Sub Workbook_Open()

Dim lResult As Long
Dim addin As COMAddIn
For Each addin In Application.COMAddIns
If addin.progID = "SBOP.AdvancedAnalysis.Addin.1" Then
If addin.Connect = False Then addin.Connect = True
End If
Next

' Put here your "Application.Run" code

End Sub

Regards, Martin