Publish Excel Workbook with Power Pivot To Power BI

PowerShell Help
1

Hi guys,
The following script opens excel workbook, refresh power pivot then publish excel to Power BI.
I’m having an issue figuring out how I’m supposed to write this syntax in PowerShell (remember this syntax is VBA)

ActiveWorkbook.PublishToPBI PublishType:=msoPBIUpload, nameConflict:=msoPBIOverwrite, bstrGroupName:="Business Intelligence Team"

Here is my full PowerShell script

$Excel = New-Object -ComObject excel.application 
$Excel.visible = $true #$false 
$File = "C:\Users\MyUserName\Daily Operations Report.xlsx"
$Workbook = $Excel.Workbooks.open($File)
$Workbook.Model.Refresh()

#This is where it's throwing an exception 
$Workbook.PublishToPBI PublishType:=msoPBIUpload, nameConflict:=msoPBIOverwrite, bstrGroupName:="Business Intelligence Team"





#$Excel.Workbooks.close() 

#$Excel.Quit() 

[System.Runtime.InteropServices.Marshal]::ReleaseComObject($Excel) | Out-Null

Thank you for your help

2

Guess:

$Workbook.PublishToPBI(msoPBIUpload, msoPBIOverwrite, "Business Intelligence Team")

But I can’t tab complete to that method when I try it.

3

I got the following error

At line:1 char:24
+ $Workbook.PublishToPBI(msoPBIUpload, msoPBIOverwrite, "Business Intel ...
+                        ~
Missing ')' in method call.
At line:1 char:24
+ $Workbook.PublishToPBI(msoPBIUpload, msoPBIOverwrite, "Business Intel ...
+                        ~~~~~~~~~~~~
Unexpected token 'msoPBIUpload' in expression or statement.
At line:1 char:36
+ $Workbook.PublishToPBI(msoPBIUpload, msoPBIOverwrite, "Business Intel ...
+                                    ~
Missing argument in parameter list.
At line:1 char:83
+ ... ishToPBI(msoPBIUpload, msoPBIOverwrite, "Business Intelligence Team")
+                                                                         ~
Unexpected token ')' in expression or statement.
    + CategoryInfo          : ParserError: (:) [], ParentContainsErrorRecordException
    + FullyQualifiedErrorId : MissingEndParenthesisInMethodCall
4

How about this way:

$Workbook.PublishToPBI('msoPBIUpload', 'msoPBIOverwrite', "Business Intelligence Team")

I get:

Could not get dispatch ID for PublishToPBI (error: 0x80010108)).
At line:1 char:1
+ $Workbook.PublishToPBI('msoPBIUpload', 'msoPBIOverwrite', "Business I ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OperationStopped: (:) [], MissingMemberException
    + FullyQualifiedErrorId : System.MissingMemberException
5

I get the following error

Exception from HRESULT: 0x800A03EC
At line:1 char:1
+ $Workbook.PublishToPBI('msoPBIUpload', 'msoPBIOverwrite', "Business I ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OperationStopped: (:) [], COMException
    + FullyQualifiedErrorId : System.Runtime.InteropServices.COMException
6

Just so you know if I use the method without any parameter, it gets publish

$Workbook.PublishToPBI()

However, I want to publish it with the parameter and specify which group it’s published under.

Thanks

7

I used the enum value instead of the string and it worked.

here is the final syntax

$Workbook.Model.Refresh()
$Workbook.PublishToPBI(1,2,'Business Intelligence Team')

Thanks everyone

8

Hmm, I wonder how you get those enums in powershell.

9

you don’t.
Powershell doesn’t have them.
I had to test the method first in excel vba then I went to the definition of each parameter to understand the parameters values.
This is how I did it in excel