Automating Excel to Power BI Publishing

Excel 2016 added a Publish to Power BI menu to let you export or connect Excel workbooks to Power BI. You can read more about this Excel feature here. One area where Excel is still ahead of Power BI Desktop is that is has an object model that lets you automate tasks with VBA. Unfortunately, Power BI Desktop doesn’t have an object model so you have to resort to unsupported ways (aka hacks) to automate tasks, such as refreshing and publishing to Power BI. I discussed some here.

I’ve noticed that Microsoft added not yet documented PublishToPBI method to Excel 2016. With it, refreshing the Excel data model and publishing it to Power BI Service takes two lines of code (you’d need more code to open the Excel workbook from an external application).

Sub Macro1()

ActiveWorkbook.Model.Refresh

ActiveWorkbook.PublishToPBI PublishType:=msoPBIExport, nameConflict:= msoPBIAbort, bstrGroupName:=”<Some Workspace>”

End Sub