cancel
Showing results for 
Search instead for 
Did you mean: 

Bringing up the Prompts in a Macro

0 Kudos

Hi,

I am using Analysis for Office Version 1.4.0.2528.

I have written some VBA code to "reformat" the report after a query is prompted (to change some variables) and refreshed.

I  would like to add to the beginning of the macro to force the display of the prompts (rather than clicking on "prompts" and then running the macro) and then proceed to run the rest of the macro.

I have tried some variations of SAPExecuteCommand but have not been successful.

Any help would be appreciated.

Thanks,

Alvin

Accepted Solutions (1)

Accepted Solutions (1)

former_member110741
Product and Topic Expert
Product and Topic Expert
0 Kudos

Hi Alvin Toh,

you could use below VBA to display prompt

lResult = Application.Run("SAPExecuteCommand", "ShowPrompts", "ALL")

another option  in Display panel under components tab you have options to display the force prompt

PS: you are using very old version of AO SP0, currently SP10 is available in SMP, if possibly you may use latest one

Best Regards,

Subhash

0 Kudos

Thanks Venkata.

I did try the code but nothing seems to happen. I did step through and the code did not throw up an error.....do i need Callbacks?

I also seem to recall reading somewhere that Prompts became available in SP 4/8.

And yes, I wish I have the latest pack but my company has not been tested it.

Cheers,

Alvin

former_member110741
Product and Topic Expert
Product and Topic Expert
0 Kudos

Hi Alvin Toh,

I did not see any info about prompts in 1.4 SP0 documentation,

you may need to upgrade to latest one in order to display the prompt via API

currently with SP10 it works fine with above code and it displays prompt

Subhash

Former Member
0 Kudos

actually 1.4. SP4 is the first version which contains the API for opening the prompt

Regards

Thorsten

Former Member
0 Kudos

Hi Alvin,

almost all guys here make the same mistake... so it's likely you have the same issue:

BEFORE you can call the prompts screen via VBA, you have to refresh the DS once!

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

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

Feel free to blame SAP that "ShowPrompts" has no incorporated refresh functionality 😉

Best regards,

Martin

0 Kudos

Thanks Thorsten,

I did think I saw that somewhere about Prompts starting from SP4.

Cheers, Alvin

0 Kudos

Thanks Martin,

That did help somewhat....when I set "Force Prompts for Initial Refresh".

But because I am on Version 1.4.0.2528, it will bring up the prompt when I run the macro for the first time but does not work for subsequent refreshes.

Cheers, Alvin

daniel_grber
Explorer
0 Kudos

Hello,

this code works for us. We use "RefreshData" instead of "Refresh". Additionally we have a loop if variables are merged:

Dim lResult As Long
lResult = Application.Run("SAPExecuteCommand", "ShowPrompts", "DP_11")
ErrorCode = Application.Run("SAPGetProperty", "LastError", "Number")
If ErrorCode = 37 Then 'DataSource is not yet refreshed
    lResult = Application.Run("SAPExecuteCommand", "RefreshData", "DP_11")
ElseIf ErrorCode = 36 Then 'Variables are Merged
    Click = MsgBox("Variables are merged! Do you want to refresh all queries in the workbook?", vbOKCancel, "Warning")
    If Click = 1 Then
    lResult = Application.Run("SAPExecuteCommand", "RefreshData", "All")
    End If

End If


Regards

Daniel

0 Kudos

Thanks Daniel.

But it did not work for my Service Pack.

As a matter of interest.....what version and SP are you on?

Cheers, Alvin

Answers (0)