通过

Automatically refreshing your Excel reports

  • 项目

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

Comments

  • Anonymous
    February 02, 2016
    I'd suggest removing this as this no longer works, and is not supported by Microsoft. Excel is an interactive application and you fail to mention that this only works when you're logged in.