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?
