Powershell to refresh Excel with OLAP Query. Credential issues

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.  
           $excelworkbook.RefreshAll()  
           # The following script lines will Save the file.  
           $excelworkbook.Save()  
           $excelworkbook.Close()
           Write-Host "Update Complete " $workbookpath  
           
      }  
 $excel.quit()  
  

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?