Powershell to refresh Excel with OLAP Query. Credential issues

Copper Contributor

Hi Experts,


I'm rather new with PowerShell scripting so please be patient with me :)


I have multiple excel file with OLAP Query connections connecting to Power BI Datasets, following are the script;




 $libraryPath = "C:\Repos\AUSD\3.0\Test"  
 $excel = new-object -comobject Excel.Application 
 $excel.Visible = $false
 # Give delay to open  
 Start-Sleep -s 3  
 $allExcelfiles = Get-ChildItem $libraryPath -recurse -include “*.xls*”  
 foreach ($file in $allExcelfiles)  
      $workbookpath = $file.fullname  
           Write-Host "Updating " $workbookpath  
           # Open the Excel file  
           $excelworkbook = $excel.workbooks.Open($workbookpath)  
           $connections = $excelworkbook.Connections  
           # This will Refresh All the pivot tables data.  
           # The following script lines will Save the file.  
           Write-Host "Update Complete " $workbookpath  




It is working fine if following options;

$excel.Visible is true


However this is going to be scheduled in the server and hopefully this could be done in the background, hence the $excel.Visible = $false


This causing the following error;



I suspect this is due to the Automatic sign in which happen when the Excel are open, due to its not being open, its failing the sign in process.




Here come the question, how do I bypass or rather set the credentials/permission right?

2 Replies

@ZuheirAshraf Did you eventually solve the issue? I have a similar problem and would be very happy to see the solution!

@JKrook Unfortunately, I couldn't find a way to bypass the UI sign in. So we ended up setting an agent machine to run the script with visible option true with the excel already pre-sign in with specified credentials. 


Up till now seems to work fine and doing its job on daily basis.