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.  
           $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;

image.pngimage.png

 

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.