title: Refreshing Excel Sheets using powershell
body: <p>I have 10 excel different excel sheet in one folder named test. I want to refresh the data connection and data in the pivot table using windows power shell script. The process is using for each loop open each file and then in the file choose powerpivot menu refresh all data there which will refresh the OLEDB and OLAP query and then close that page and go to the data tab and click on the refresh all button and after refreshing work book in each excel sheets there are 5 sheets in 4 out of 5 sheets B4 indicates the month in slicer. we need to choose previous month in all 4 sheets than save the excel.xlsx file and go to the next file do the same process. I have the basic code if someone can help to modify the code. Sheet 1 Sheet 3 Sheet 4 Sheet 5 has the slicer. </p>
  
  <pre><code>#Set the file path (can be a network location)
  $filePath = "C:\test"
  
  
  $excelObj = New-Object -ComObject Excel.Application
  
  $excelObj.Visible = $true
  
  #Open the workbook
  $workBook = $excelObj.Workbooks.Open($filePath)
  
  #Focus on the top row of the "Data" worksheet
  $workSheet = $workBook.Sheets.Item("PowerPivot")
  $workSheet.Select()
  $workSheet.PowerPivotWindow
  foreach ($file in $excelfiles)
  {
  $workSheet = $workBook.Sheets.Item("Home")
  $workSheet.Select()
  $workBook.RefreshAll()
  $excelworkbook.Close()
  
  $workSheet = $workBook.Sheets.Item("Data")
  $workSheet.Select()
  
  #Refresh all data in this workbook
  $workBook.RefreshAll()
  
  
  $workBook.Save()
  $excelworkbook.Close()
  }
  
  #Uncomment this line if you want Excel to close on its own
  $excelObj.Quit()
  </code></pre>