Publish Excel file to PowerBI using VBA

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

Advertisements