Refreshing data connection in Excel using VBScript

I have to present some statistical information on SharePoint using Excel Web Access web part. After a whole week of data consolidation from several different external sources, I have realized that the data connections does not get refreshed when SharePoint loads the web part. All the refreshing options that can be set in Excel (Excel 2007 in my case) does not help but giving more errors.
After hours of Google researching, many suggested to follow the steps set out in this article:
Plan external data connections for Excel Services

Surely it’s probably the proper way to do it, but I just don’t have the time to go through the million steps. Since I am more of a developer than a site administrator, I have decided to write a script to refresh the data connections in the Excel workbook instead. The script will be run on a daily basis which works for me in this case.

Here is the VBScript that I have created:

' VB Script Document
option explicit

refreshPivotTable("[file path][excel file name]")

Sub refreshPivotTable(strExcel)
' Load Excel object
Dim objExcel
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
objExcel.Application.DisplayAlerts = False
objExcel.Workbooks.Open strExcel

' Refresh PivotTables
Dim pt
Dim i
Dim nShCount

nShCount = objExcel.ActiveWorkbook.Sheets.Count
For i = 1 To nShCount
For Each pt In objExcel.ActiveWorkbook.Sheets(i).PivotTables
pt.RefreshTable()
Next
Next

' Close Excel object
objExcel.ActiveWorkbook.Save
objExcel.Quit
Set objExcel = Nothing

Schedule It
You can now schedule to run this using the Windows task scheduler. Run it as “cscript vbExcelScrip.vbs”

Data Source File
One thing to note that you might have to unlink the physical data source file with your Excel. A data source file is created by Excel (normally saved on your My Document > My Data Sources) when you set up a data connection. However, the connection information is in fact saved in your Excel, the connection file is used as a backup and reusable by other workbooks.
Depending on what user you are using to run the scheduled task, it might not have access to the data connection file on your My Document folder. To unlink the data connection file from your Excel workbook:

  1. go to Data > Connections
  2. Click the “Properties…” button
  3. Under the “Definition” tab
  4. Uncheck “Always use connection file”

Hopefully this issue is addressed in SharePoint 2010 and Office 2010. 🙂

Spread the word. Share this post!

1 comment on “Refreshing data connection in Excel using VBScript”

  1. Keith Truesdell

    AWESOME!!!
    Love this….this is what I was looking for, not a VBA Solution but a real VBScript Solution.
    I have a dozen reports that need to be refreshed every morning when I come in and the lines of business have decided that it was too much to open them and click "refresh" so I did try to set it up so it would auto-refresh when opened….but that was too much….SO, i use this then I have an "email" function that emails the report after the refresh to group of people.
    THANK YOU!

Leave A Reply

Your email address will not be published. Required fields are marked *