Power Query / Workbookqueries

[Joris van der Straten]

Hi,

Currently it's possible to access workbook queries by using VBA code like:

Dim qry As WorkbookQuery    

If (ThisWorkbook.Queries.Count = 0) Then

When adding this kind of VBA code to Excel DNA and running it from C# it says Type 'WorkbookQuery' is not defined.

I know there are some differences between Excel DNA and the regular VBA things like ActiveWorkbook. 

In future, will it be possible to access the workbook queries in C#?

Thanks for all good work so far Govert!

[Govert van Drimmelen]

Hi Joris,

You have full access to the Excel COM object model from inside your Excel-DNA add-in.

You need to add a reference to the primary interop assemblies (Microsoft.Office.Interop.Excel.dll and Office.dll).

Then you need to get hold of the root Application object with a call to ExcelDnaUtil.Application (you can cast the result to type Microsoft.Office.Interop.Excel).

So your code would be something like:

using Microsoft.Office.Interop.Excel;

public static class MyMacros

{

    [ExcelCommand(MenuName="My AddIn", MenuText="Run Macro")] // Or via a context menu, a shortcut or a ribbon button

    public static void MyMacro()

    {

        Application xlApp = (Application)ExcelDnaUtil.Application;

        Workbook wb = xlApp.ActiveWorkbook;

        // ... etc
    }

}

The WorkbookQuery class you ask about was only added in Excel 2013. So to make it available you'll need to add a Reference to the primary interop assembly from that Office version. Under Excel 2013 and later, you might have to select some option in the Office installer to make that reference available. If you keep the 'Embed Interop Types' option enabled, you need not distribute anything extra with your add-in.

I hope that gives you a start.

Regards,

Govert