Refresh Excel connections

Question

I'm trying to loop through each connection in my Excel workbook and refresh each one individually and capture any error messages in between each refresh. However, I receive a 'Type-Mismatch' error when trying to run the code:

Private Sub btnRefreshConns_Click()
Dim cn As WorkbookConnection
Set cn = ActiveWorkbook.Connections.Count

For Each cn In Workbook.Connections

cn.Refresh

Next

End Sub

Could someone please assist me?

Answer 1

Private Sub btnRefreshConns_Click()
Dim cn As WorkbookConnection
'Remove this line --> Set cn = ActiveWorkbook.Connections.Count

For Each cn In ActiveWorkbook.Connections

    cn.Refresh

Next

End Sub

should do it. With For Each, you don't need to keep track of the count.

(Note: apostrophes ' introduce comments in VB, so you can try commenting out offending lines to see what happens.)

edit: The loop needs to refer to ActiveWorkbook. Workbook is a type, i.e., what kind of thing ActiveWorkbook is. ActiveWorkbook is an object, an actual thing you can manipulate.