CODEWITHSUNDEEP
vbaexcelessbase
Naresh Abburi
Naresh Abburi

Reputation: 259

How to Refresh Excel Smart View Essbase using Macro

I'm using below code to refresh Essbase feeds in my workbook and it is working nicely, however, the only downfall is that I need to enter password every time I refresh the essbase as our Essbase system is highly secured.

My question is, is it possible to incorporate the Password in the macro so that I don't have to enter the password every time I refresh the feeds.? Solving this problem would also enable me to automate this whole process through Python and schedule a job. 

Private Declare PtrSafe Function HypMenuVRefreshAll Lib "HsAddin" () As Long


Sub RefreshHFM()

Call HypMenuVRefreshAll

End Sub

Any help.?

Thanks.

Upvotes: 1

Views: 8681

Answers (1)

jwj
jwj

Reputation: 521

The HypMenuVRefreshAll command is basically the equivalent of clicking on the refresh button, and of course it is going to prompt you for a password because that's exactly what would happen if you clicked on the menu yourself. There are other commands, however, for the other menu items as well as the actual API that can be used to connect. You can connect with the following code:

Private Sub cmdConnect_Click()
    Dim lReturn As Long
    Dim sMessage As String

    ''' try to connect
    lReturn = EssVConnect("sheet name", "admin", "password", "epmvirt11124", "sample", "basic")

    ''' show a message if necessary
    If lReturn <> 0 Then
        sMessage = EssVGetLastErrorMessage()
        MsgBox "EssVConnect status = " & lReturn & ".  Error Message = " & sMessage
    End If
End Sub

I have borrowed this code from a button that connects to a specific cube. Be sure to update the username, password, server name, application, and cube to match your environment.

Please also note that this is part of the "old" Essbase VB API that works with the "classic" Excel add-in. The code is different for Smart View, which came with a completely different VB API.

Upvotes: 0

Related Questions