Thursday, December 13, 2012

Powershell script to Refresh Excel Data

The following sript opends Excel files one by one, refreshes the data, saves the file back and closes the file.

 Function RefreshExcelData($Excel){
  $App = new-object -comobject excel.application
  $App.Workbooks.Open($Excel)
  $App.Visible = $true
  $App.ActiveWorkbook.Refreshall()
  $App.Save()
  $App.Quit()
  $username = $env:username
  Remove-Item "C:\Users\$username\Documents\RESUME.XLW"
 }
 CLEAR-HOST
 $Host.UI.RawUI.ForegroundColor = "yellow"
 $Excel = 'R:\folder\file1.xlsx'
 RefreshExcelData $Excel
 $Excel = 'R:\folder\file2.xlsx'
 RefreshExcelData $Excel
 $Excel = 'R:\folder\file3.xlsx'
 RefreshExcelData $Excel
 $Host.UI.RawUI.ForegroundColor = "white"

If you find this script useful please donate generously.

No comments:

Post a Comment