Running Consolidation from Excel with VBA

[Jmorgan]

Is it possible to run a consolidation from Excel, is there a VBA that can do this?

[rjgoss]

According to the online documentation, there's not really one for this, but there is a workaround that can be done in Excel that works, but comes with a couple caveats.  The steps are below and then the caveats.

1)  In Excel, go to File=>Options=>Quick Access Toolbar=>Choose commands from: OneStream Tab.  Find the one that looks like this  (there are no labels) and press the Add>> button.  Press the OK button to close the Excel Options window.

2)  Press the Alt button to see what the keyboard shortcut key value is.   In this example, it is 5.  Remember and/or write down this value.

3)  Your file will have to be saved as a .xlsm file.  Create a macro that does whatever you need it to do besides the consolidation.

4)  Add the command below.

Application.SendKeys "%5%"

This tells Excel to do the equivalent of pressing the Alt and 5 keys (5 needs to be replaced with whatever Quick Access Toolbar key value yours is).  It will run a consolidation and you can see it in Task Activity.

CAVEATS

1)  Be very careful with the SendKeys command.  It will run the command on whatever the active window is, no matter the program.  Therefore, make sure you put in code to activate the proper workbook and worksheet and select a cell that has the POV of the consolidation you want to run BEFORE you run the SendKeys command.

2)  This is not a transferable macro as every user can have a different setup in Excel.  The macro would need to be updated for different users.

[Jmorgan]

I'm not even able to get the consolidation button to highlight in the excel add-in. I think I need to figure that part out first, any idea on how to activate that?

[rjgoss]

Are you logged in?  If so, try refreshing the Quick View or Cube View.  I don't know why, but sometimes you also need to click on a cell outside the CV or QV, then back on a cell inside it for buttons to become active.