Powershell to refresh Excel with OLAP Query. Credential issues

%3CLINGO-SUB%20id%3D%22lingo-sub-901512%22%20slang%3D%22en-US%22%3EPowershell%20to%20refresh%20Excel%20with%20OLAP%20Query.%20Credential%20issues%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-901512%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Experts%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20rather%20new%20with%20PowerShell%20scripting%20so%20please%20be%20patient%20with%20me%20%3A)%3C%2Fimg%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20multiple%20excel%20file%20with%20OLAP%20Query%20connections%20connecting%20to%20Power%20BI%20Datasets%2C%20following%20are%20the%20script%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%20%24libraryPath%20%3D%20%22C%3A%5CRepos%5CAUSD%5C3.0%5CTest%22%20%20%0A%20%24excel%20%3D%20new-object%20-comobject%20Excel.Application%20%0A%20%24excel.Visible%20%3D%20%24false%0A%20%23%20Give%20delay%20to%20open%20%20%0A%20Start-Sleep%20-s%203%20%20%0A%20%24allExcelfiles%20%3D%20Get-ChildItem%20%24libraryPath%20-recurse%20-include%20%E2%80%9C*.xls*%E2%80%9D%20%20%0A%20foreach%20(%24file%20in%20%24allExcelfiles)%20%20%0A%20%7B%20%20%0A%20%20%20%20%20%20%24workbookpath%20%3D%20%24file.fullname%20%20%0A%20%20%20%20%20%20%20%20%20%20%20Write-Host%20%22Updating%20%22%20%24workbookpath%20%20%0A%20%20%20%20%20%20%20%20%20%20%20%23%20Open%20the%20Excel%20file%20%20%0A%20%20%20%20%20%20%20%20%20%20%20%24excelworkbook%20%3D%20%24excel.workbooks.Open(%24workbookpath)%20%20%0A%20%20%20%20%20%20%20%20%20%20%20%24connections%20%3D%20%24excelworkbook.Connections%20%20%0A%20%20%20%20%20%20%20%20%20%20%20%23%20This%20will%20Refresh%20All%20the%20pivot%20tables%20data.%20%20%0A%20%20%20%20%20%20%20%20%20%20%20%24excelworkbook.RefreshAll()%20%20%0A%20%20%20%20%20%20%20%20%20%20%20%23%20The%20following%20script%20lines%20will%20Save%20the%20file.%20%20%0A%20%20%20%20%20%20%20%20%20%20%20%24excelworkbook.Save()%20%20%0A%20%20%20%20%20%20%20%20%20%20%20%24excelworkbook.Close()%0A%20%20%20%20%20%20%20%20%20%20%20Write-Host%20%22Update%20Complete%20%22%20%24workbookpath%20%20%0A%20%20%20%20%20%20%20%20%20%20%20%0A%20%20%20%20%20%20%7D%20%20%0A%20%24excel.quit()%20%20%0A%20%20%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20is%20working%20fine%20if%20following%20options%3B%3C%2FP%3E%3CP%3E%3CEM%3E%24excel.Visible%3C%2FEM%3E%20is%20true%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%20this%20is%20going%20to%20be%20scheduled%20in%20the%20server%20and%20hopefully%20this%20could%20be%20done%20in%20the%20background%2C%20hence%20the%26nbsp%3B%3CEM%3E%24excel.Visible%20%3D%20%24false%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20causing%20the%20following%20error%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20496px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F136042i41ACD70C41BAD9B7%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20557px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F136043i78B327AB6CB46EEE%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20suspect%20this%20is%20due%20to%20the%20Automatic%20sign%20in%20which%20happen%20when%20the%20Excel%20are%20open%2C%20due%20to%20its%20not%20being%20open%2C%20its%20failing%20the%20sign%20in%20process.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E--------------%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20come%20the%20question%2C%20how%20do%20I%20bypass%20or%20rather%20set%20the%20credentials%2Fpermission%20right%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-901512%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMicrosoft%20Excel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EWindows%20PowerShell%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1467743%22%20slang%3D%22en-US%22%3ERe%3A%20Powershell%20to%20refresh%20Excel%20with%20OLAP%20Query.%20Credential%20issues%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1467743%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F422698%22%20target%3D%22_blank%22%3E%40ZuheirAshraf%3C%2FA%3E%26nbsp%3BDid%20you%20eventually%20solve%20the%20issue%3F%20I%20have%20a%20similar%20problem%20and%20would%20be%20very%20happy%20to%20see%20the%20solution!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1479914%22%20slang%3D%22en-US%22%3ERe%3A%20Powershell%20to%20refresh%20Excel%20with%20OLAP%20Query.%20Credential%20issues%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1479914%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F700891%22%20target%3D%22_blank%22%3E%40JKrook%3C%2FA%3E%26nbsp%3BUnfortunately%2C%20I%20couldn't%20find%20a%20way%20to%20bypass%20the%20UI%20sign%20in.%20So%20we%20ended%20up%20setting%20an%20agent%20machine%20to%20run%20the%20script%20with%20visible%20option%20true%20with%20the%20excel%20already%20pre-sign%20in%20with%20specified%20credentials.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUp%20till%20now%20seems%20to%20work%20fine%20and%20doing%20its%20job%20on%20daily%20basis.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New 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
Highlighted

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

Highlighted

@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.