Forum Discussion

Cosimo's avatar
Cosimo
Contributor II
4 years ago

Excel VBA automation - Refresh Workbook function

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 o...
Excel Add In
PhilB's avatar
PhilB
New Contributor III
4 years ago

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
  • Cosimo's avatar
    Cosimo
    Contributor II
    4 years ago

    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
     
    • MarkHoughton's avatar
      MarkHoughton
      Contributor
      4 years ago

      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.