Excel VBA automation - Refresh Workbook function

Cosimo
Contributor II

SOURCE: CHAMPIONS

Does anyone know if we can call the Refresh Workbook function using VBA? I’m looking to introduce a button on worksheets that performs the Refresh Workbook option that’s located on the OneStream XF ribbon.

Cosimo_0-1641844028987.png

 

9 REPLIES 9

PhilB
New Contributor III

Hi Cosimo,

There is a OS Knowledge Base article about this: KB0010071

https://onestreamsoftware.service-now.com/sp/?sys_kb_id=09389b291be7601099ce86e1604bcb5d&id=kb_artic...

In practice, it looks something like this:

Sub RefreshXFFunctions()
Set XFAddIn = application.COMAddIns("OneStreamExcelAddIn")
If Not XFAddIn Is Nothing Then
    If Not XFAddIn.Object Is Nothing Then
        Call XFAddIn.Object.RefreshXFFunctions
    End If
End If

End Sub

or

Sub RefreshCubeViews()
    Set XFAddIn = application.COMAddIns("OneStreamExcelAddIn")
    If Not XFAddIn Is Nothing Then
        If Not XFAddIn.Object Is Nothing Then
            Call XFAddIn.Object.RefreshCubeViews
        End If
    End If
End Sub
Phil Brosnan
pbrosnan@finit.com | finit.com

Cosimo
Contributor II

Thank Phil. This is exactly what I was looking for. Interesting to note that I had to run 3 functions to simulate “Refresh Workbook”:

> Sub Refresh_Workbook()
>     Set XFAddin = Application.COMAddIns("OneStreamExcelAddIn")
>     If Not XFAddin Is Nothing Then
>         If Not XFAddin.Object Is Nothing Then
>             Call XFAddin.Object.RefreshXFFunctions
>             Call XFAddin.Object.RefreshQuickViews
>             Call XFAddin.Object.RefreshCubeViews
>         End If
>     End If
> End Sub
 

Thanks Cosimo for this extra info, also I found that in the Preferences... Enable Macros for XF Event Processing needed to be set to FALSE for it to work.

MarkHoughton_0-1648634302269.png

 

OS_Pizza
Contributor III

With an Okta user you could try the following:

' Set Excel Addin object
Set xfAddin = Application.COMAddIns("OneStreamExcelAddIn").Object

'OneStream Web URL
url = "https://server/OneStreamWeb"

'okta user credentials
oktaUsername = "enter okta username"
oktaPassword = "enter okta password"

' app name
app = "enter application name"

' get SSO Token
ssoToken = xfAddin.ProcessSSOAuthenticationAndCreateToken(url, oktaUsername, oktaPassword)

' display token, only use for validating, if no token exists then login will not be successful
MsgBox ssoToken

' Get user from SSO token
user = xfAddin.GetXFUserNameFromSSOToken(ssoToken)

' log into application using token
isLoggedIn = xfAddin.LogonAndOpenApplication(url, user, ssoToken, app)

If isLoggedIn Then

MsgBox ("Logged into OneStream")

Refresh = xfAddin.RefreshQuickViewsForActiveWorksheet()

xfAddin.Logoff

Else
MsgBox ("Problem with login")
End If

Thanks for this very useful piece of code, it certainly works for my OKTA sign in.

Would this work for someone who has an Azure account? I am trying to log in with my Azure but I do not appear to be getting through.

SaurinPatel
New Contributor III

any example for PingFed sing in?

Can you describe your requirement in detail?

SaurinPatel
New Contributor III

I want to automate OneStream refresh, but for authentication we use PingFed.