Publish Excel Workbook with Power Pivot To Power BI

This topic contains 8 replies, has 2 voices, and was last updated by  Codernator 2 months, 1 week ago.

  • Author
    Posts
  • #95582

    Codernator
    Participant

    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

    Guess:

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

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

    • #95693

      Codernator
      Participant

      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

      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

      Codernator
      Participant

      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

      Codernator
      Participant

      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

      Codernator
      Participant

      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

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

    • #95921

      Codernator
      Participant

      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

You must be logged in to reply to this topic.