Contributor

Excel VBA automation - Refresh Workbook function

9 replies
35 views

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.

Excel Add-In

P

PhilB

Member

Hi Cosimo,

There is a OS Knowledge Base article about this: KB0010071

https://onestreamsoftware.service-now.com/sp/?sys_kb_id=09389b291be7601099ce86e1604bcb5d&id=kb_article_view&sysparm_rank=1&sysparm_tsqueryId=9ec76c471b6a381099ce86e1604bcb0c

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

C

CosimoAuthor

Contributor

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

M

MarkHoughton

Contributor

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.