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.COMAddIn...
Excel Add In
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

        Hello All, I am trying to avoid the step where user manually login in the excel OneStream Ribbon and then perform the Refresh Sheet with the macro button.

        Is there any function to login in the VBA code before the refresh..