Publish Excel Workbook with Power Pivot To Power BI

Welcome Forums General PowerShell Q&A Publish Excel Workbook with Power Pivot To Power BI

This topic contains 8 replies, has 2 voices, and was last updated by

 
Participant
8 months ago.

  • Author
    Posts
  • #95582

    Participant
    Points: 0
    Rank: Member

    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

  • #95636
    js

    Participant
    Points: 202
    Helping Hand
    Rank: Participant

    Guess:

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

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

    • #95693

      Participant
      Points: 0
      Rank: Member

      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
      
    • #95694
      js

      Participant
      Points: 202
      Helping Hand
      Rank: Participant

      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
      
    • #95697

      Participant
      Points: 0
      Rank: Member

      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
      
    • #95700

      Participant
      Points: 0
      Rank: Member

      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

    • #95843

      Participant
      Points: 0
      Rank: Member

      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

    • #95844
      js

      Participant
      Points: 202
      Helping Hand
      Rank: Participant

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

    • #95921

      Participant
      Points: 0
      Rank: Member

      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

The topic ‘Publish Excel Workbook with Power Pivot To Power BI’ is closed to new replies.