Calling DaxStudio Excel addin from VBA

Aug 21, 2015 at 9:18 AM
I have created a custom add-in with macros to automate frequent tasks related to power pivot models (documenting measures, calc columns...etc).
I would like to add to my custom ribbon UI a button to launch Dax Studio which would save me switching the default add-in tab in the Excel ribbon.
So far I can create the COMaddin object in vba but I am stuck at what to do next to launch DaxStudio UI.
Could you help ?
Set addIn = Application.COMAddIns("DaxStudio.ExcelAddIn")
????
Coordinator
Aug 22, 2015 at 2:19 AM
Dax Studio is written in C# as a VSTO add-in. I'm not sure how or even if you can launch it from VBA. But if all you want is to move the button onto another ribbon you can do this by right clicking on the ribbon and the choosing the "Customize Ribbon" option and use the move buttons to move the existing button to another ribbon.
Aug 24, 2015 at 7:24 AM
Unfortunately, from Excel UI, you cannot move or copy a button to a custom ribbon - at least I have not found how. According to this article, exposing methods to vba requires bespoke code. But this is a nice-to-have.
Coordinator
Aug 24, 2015 at 8:06 AM
I think you can. I just clicked on the customize ribbon, created a custom ribbon tab and moved Dax Studio to it. I had to move the whole button group (which was labelled "<< No Label >>") but there is only one button in that group, so that worked fine.
Aug 24, 2015 at 10:02 AM
This is true for custom tabs that you create with the Excel UI. But it does not seem possible for document-specific custom tabs which are created by means of UI XML code (details here).
In any case, I can live with the current situation. The benefit of DAX studio is well worth the couple of clicks required to launch it !
Coordinator
Aug 24, 2015 at 12:45 PM
Oh I see. I did not realise that you were working with that sort of ribbon. I did a quick search and came across this https://msdn.microsoft.com/en-us/library/bb608621.aspx So I don't think you can launch Dax Studio without us changing some of the code in our addin. However I've added the code to my current dev branch and I was able to launch Dax Studio with the following VBA:
Sub CallVSTOMethod()
    Dim addIn As COMAddIn
    Dim automationObject As Object
    Set addIn = Application.COMAddIns("DaxStudio")
    Set automationObject = addIn.Object
    automationObject.Launch
End Sub
So this will be part of the v2.3.0 release, but it may be a few more weeks before we get that fully stabilized.
Aug 24, 2015 at 1:41 PM
Great !