Recently Teo Lachev released a very short but important post about Automation Excel to PowerBI publishing.
In a nutshell: now we have a VBA method PublishToPBI, which allows to publish workbooks to PowerBI.
This is quite interesting scenario for Excel-based Self-Service BI solutions.
I played around with this method, and share with you results of my experiments.
Excel VBA PublishToPBI method documentation
Unfortunately, there is no official documentation yet. At least in the list of Workbook Methods.
Below is my modest attempt.
Purpose
Publish workbook to PowerBI as a report or dataset.
Requirements
- workbook must be saved in xlsx or xlsm format.
- User with PowerBI license must be signed-in in Excel.
- for publishing as a dataset – workbook must have Excel Table or Data Model.
Syntax
expression.PublishToPBI( PublishType, nameConflict, bstrGroupName )
expression a variable that represents a Workbook object.
Parameters
Name |
Required/Optional |
Data Type / Description |
PublishType |
Optional |
Enum XlPublishToPBIPublishType
msoPBIExport = 0, publish workbook as a dataset. Workbook must contain Table Data (range formatted as a Table) or Data Model (aka PowerPivot model).
msoPBIUpload = 1 (default) publish workbook as a report, which can be consumed in Excel Online directly in PowerBI. |
nameConflict |
Optional |
Enum XlPublishToPBINameConflictAction
msoPBIAbort = 1 (default), workbook will not be loaded to PowerBI if there already exists workbook with same full name (including extension)
msoPBIIgnore = 0, ignores existence of report / dataset and creates new one on Upload/Export.
msoPBIOverwrite = 2, overwrites existing report / dataset with same full name of workbook. Note: method raises an error if find more than one report / dataset with same full name (e.g. if msoPBIIgnore was used previously). |
bstrGroupName |
Optional |
String
Name of target Workspace.
If omitted, method loads workbook to “My Workspace” of signed-in user.
Note: requires Pro-account, signed-in user must have edit rights in target workspace. |
Example
' Creates Dataset from ActiveWorkbook in workspace "Finance Team" (if signed in user has access to it)
ActiveWorkbook.PublishToPBI nameConflict:=msoPBIOverwrite, bstrGroupName:="Finance Team", PublishType:=msoPBIExport
' Creates PowerBI Report from ActiveWorkbook in signed in user's workspace
ActiveWorkbook.PublishToPBI nameConflict:=msoPBIOverwrite, PublishType:=msoPBIUpload
' Creates Dataset from ActiveWorkbook in signed in user's workspace
ActiveWorkbook.PublishToPBI nameConflict:=msoPBIOverwrite, PublishType:=msoPBIExport
Some Details
Continue reading →