Many organization have lots of Excel reports and pivot tables whose data comes from SQL Server. As data on the backend database changes you would need to repopulate your excel books with updated information. Doing it by hand can be very tedious manual process for the reporting analyst and there are ways to automate it. The simplest way is to create a SQL Agent job which refreshes your books on a scheduled basis. VBScript below does exactly that. Just add a step to your refresh job for every report you want to refresh substituting "<path to your excel file>" with location of your excel file on some file share.

 Set oWorkBook = oExcel.Workbooks.Open("<path to your excel file>")

Now you saved yourself a whole lot time and can start working on more interesting things!

Cheers, -Yuriy

Script below

Function Main()
Dim oExcel
Dim oWorkBook
Dim oWorksheet
Dim oPivotTable
Dim IStartedExcel
 
On Error Resume Next
Set oExcel = GetObject("Excel.Application")       'will attach to an existing instance if it is there.
oExcel.Quit
 
'Instantiate excel object; disable alert messages
Set  oExcel = CreateObject("Excel.Application")
oExcel.DisplayAlerts = false
 
'when debugging set visible = true
oExcel.visible = true

'open workbook we wish to refresh
Set oWorkBook = oExcel.Workbooks.Open("<path to your excel file>")
oWorkbook.EnableConnections
oWorkbook.RefreshAll
oExcel.CalculateUntilAsyncQueriesDone
oWorkbook.Save
oWorkBook.Close
 
oExcel.Quit
Set oExcel = Nothing
 
'Main = DTSTaskExecResult_Success

End Function