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?
Transform data in excel
Hi Recently, my client has shared raw data, which I have processed a bit (attached here for reference). The workbook has 2 sheets: 1. Raw Data 2. Converted Data I have data in the format provided in Raw Data sheet and want to get the data in format provided in Converted Data sheet. Is it possible in excel to get the data in the desired format
