title: PowerShell Script to turn off "Enable background refresh" in an Excel
body: <p>I have a PowerShell script that dynamically creates, updates, and deletes excel workbooks based of a set number of of conditions. One of the processes involved in creating the excel is to run a power query. However it seems to default the setting &quot;Enable background refresh&quot; as enabled.</p>
  <p>This causes issues when running the update section of the code as the excel opens, saves, and closes before the power query can refresh.</p>
  <p>I this script is constantly creating and deleting new excels so turning off the setting manually is not viable. The only solution I can find is to add a sleep function, however when there are a lot of excels this causes performance delays.</p>
  <p><strong>Excel Setting:</strong></p>
  <p><a href="https://i.stack.imgur.com/snEyx.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/snEyx.png" alt="enter image description here" /></a></p>
  <p><strong>Update Code:</strong></p>
  <pre class="lang-bsh prettyprint-override"><code>    $File = Get-Item(&quot;$Path\$Set\$Set-main-$ReleaseDate.xlsx&quot;)
  
      $Excel = New-Object -ComObject excel.application
      $Excel.DisplayAlerts = $False
      $Excel.visible = $False
      $WorkBook = $Excel.Workbooks.Open($File.FullName)
  
      $WorkBook.RefreshAll()
  
      $WorkBook.SaveAs($File, 51)
      $WorkBook.Close($True)
  
      $Excel.Quit()
      [void][System.Runtime.Interopservices.Marshal]::ReleaseComObject($WorkSheet)
      [void][System.Runtime.Interopservices.Marshal]::ReleaseComObject($WorkBook)
      [void][System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)
      [System.GC]::Collect()
  </code></pre>