Updating Excel from Powershell as a scheduled task

by John C. Wray III Tuesday, March 29, 2016 9:26 AM

I had a script that would run a SQL stored procedure, wait, open a spreadsheet and do a refresh all. If I ran the script manually it would work just fine. As a scheduled task it would never save the spreadsheet. After some googling I found out you need to create a couple of folders.

Under C:\Windows\System32\config\systemprofile\ create a folder called Desktop

Under C:\Windows\SysWOW64\config\systemprofile\ create a folder called Desktop

After I did this, the script was able to save the updated spreadsheet.

#Set the file path (can be a network location)
$filePath = "<file path>\spreadsheet.xlsx"

#Create the Excel Object
$excelObj = New-Object -Com Excel.Application

#Wait for 10 seconds then update the spreadsheet
Start-Sleep -s 10

#Make Excel visible. Set to $false if you want this done in the background
$excelObj.Visible = $true
$excelObj.DisplayAlerts = $false

#Open the workbook
$workBook = $excelObj.Workbooks.Open($filePath)

#Wait for 10 seconds then update the spreadsheet
Start-Sleep -s 10

#Focus on the top row of the "Data" worksheet
#Note: This is only for visibility, it does not affect the data refresh
$workSheet = $workBook.Sheets.Item("Sheet1")
$workSheet.Select()

#Refresh all data in this workbook
$workBook.RefreshAll()

Start-Sleep -s 10

#Save any changes done by the refresh
$workBook.Save()
$workBook.Close()

#Uncomment this line if you want Excel to close on its own
$excelObj.Quit()
$excelObj = $null
write-host "Finished updating the spreadsheet" -foregroundcolor "green"
Start-Sleep -s 5

Tags:

Microsoft | Powershell

Comments are closed