Refresh Connections and Pivots in Excel 2007, 2010 and 2013

Question

So I've been running this VBA code in Excel 2007 without any problems:

Sub Button_Click()
    For Each objConnection In ThisWorkbook.Connections
       objConnection.Refresh
    Next
    Dim Sheet As Worksheet, Pivot As PivotTable
    For Each Sheet In ThisWorkbook.Worksheets
        For Each Pivot In Sheet.PivotTables
            Pivot.RefreshTable
            Pivot.Update
        Next
    Next
    MsgBox "Finished refreshing all data connections"
End Sub

Some of the people using my workbook are on Excel 2007, some are on Excel 2013.

The users on Excel 2013 are running into the following error:

Run-Time error "-2147417848 (80010108)":
Method "Refresh" of object "WorkbookConnection" failed

I've tried to replace the objConnection.Refresh with something along the lines of ThisWorkbook.RefreshAll but that causes Excel 2013 to crash each time its run with no errors.

All the connections have Enable Background Refresh unchecked and the connection types are Office Data Connection and Database Query.

I can't seem to find a solution that works to refresh connections in 2010 or 2013. Any help is appreciated, thanks!

Zifendale Zifendale · Accepted Answer · 2014-09-18T15:38:12

I've resolved the issue.

After posting, I questioned why my connections were different types.

It turns out that since some of my connections were pulling from a connection file on the network, it would treat them as Office Data Connections.

Once I unchecked the option to always use the connection file on the network, this changed the type to Database Query and now the refreshing script works once more.

Refresh Connections and Pivots in Excel 2007, 2010 and 2013

1 Answers