Author Posts

March 9, 2018 at 10:36 pm

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

March 10, 2018 at 11:52 pm

Guess:

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

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

March 12, 2018 at 2:29 pm

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

March 12, 2018 at 2:34 pm

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

March 12, 2018 at 2:38 pm

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

March 12, 2018 at 2:44 pm

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

March 13, 2018 at 9:39 pm

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

March 13, 2018 at 9:41 pm

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

March 14, 2018 at 3:04 pm

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