12-27-2022 11:22 AM - last edited on 05-23-2023 10:54 AM by JackLacava
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!
12-28-2022 08:37 AM
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!
12-28-2022 11:03 AM
Thank you for the suggestions! I submitted the request here https://community.onestreamsoftware.com/t5/Productivity/Table-View-VBA-Refresh/idi-p/14776#M266
03-10-2023 09:37 AM
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.
10-09-2023 02:07 AM
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..
10-09-2023 02:09 AM
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)