The OneStream Community is temporarily frozen until June 29th due to the ongoing maintenance. Please read the blog post here to learn more.

Forum Discussion

BenEppel's avatar
BenEppel
Contributor
3 years ago

Table View VBA Refresh

Hello,

I was wondering if anyone has been able to get a table view to refresh using VBA. 

I have tried the below code with no luck.

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

 

Thanks!

Excel Add In

5 Replies

  • ChrisLoran's avatar
    ChrisLoran
    Icon for OneStream Employee rankOneStream Employee
    3 years ago

    Hello, I suggest logging this as an enhancement on IdeaStream https://community.onestreamsoftware.com/ , as I don't think this has been raised yet since Table Views are relatively new to the Excel add-in (formerly being only available in the embedded Spreadsheet component).
    I investigated using Excel's native commands such as ExecuteMSO, like this

    Application.CommandBars.ExecuteMso("FileSaveAs")

    however it seems these only work for Native MSOffice command bars / ribbons, and not necessarily for third-party add-ins. I don't think the toolbar button for Refresh Table Views is exposed to the Application.CommandBars objects, and without access to the source code I don't think there is any way to find out.

    As a dirty workaround it might be possible to record a custom key sequence in Excel which relates to that button, and then have your macro issue keyboard commands (to simulate a user) but this would not be reliable if the real user presses buttons during macro execution, and I'm not even sure if the OneStream add-in toolbars can be assigned custom keyboard sequences for a specific button.

    If anyone else on the forum has any better ideas then I'm all ears! 

      • JennyCalvache's avatar
        JennyCalvache
        New Contributor III
        3 years ago

        Hello, I was able to refresh with the following codes.

        Set xfaddIn = Application.COMAddIns("OneStreamExcelAddIn")
        If Not xfaddIn Is Nothing Then
        If Not xfaddIn.Object Is Nothing Then


        xfaddIn.Object.ShowParametersDlgForActiveWorksheet
        xfaddIn.Object.RefreshXFFunctionsForActiveWorksheet

        End If
        End If

        Hopefully this one helps if anyone needs to refresh Table view. However I can't refresh Table view without using xfaddIn.Object.ShowParametersDlgForActiveWorksheet. Does anyone know how?

        Thanks in advance.

  • vignesh's avatar
    vignesh
    New Contributor II
    2 years ago

    I tried the below the code by hardcoding the details, but not working

    Set xfAddin = Application.COMAddIns("OneStreamExcelAddIn")

    loginstatus = xfAddin.LogonAndOpenApplication(url, user, password, application)