3

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?

pnuts
  • 58,317
  • 11
  • 87
  • 139
Sean
  • 442
  • 3
  • 6
  • 19

1 Answers1

5
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.

cxw
  • 16,685
  • 2
  • 45
  • 81
  • This is what I had previously. However, I was receiving an 'Object Required' error; still do so if I remove the line you suggested. – Sean Sep 07 '15 at 10:19
  • The refresh works perfectly, thank you! However, is there a way to catch any errors that may occur on each connection? – Sean Sep 07 '15 at 10:25
  • 2
    Yes: the `on error goto` statement. For the sake of future readers, would you please accept this answer and ask another question with more details about what you'd like to do once you've caught the errors? As always, you'll get better answers here if you have a crack at it first and show the code you've tried :) . Post a link to the other question here and I'll see it. – cxw Sep 07 '15 at 10:30
  • This is the question I have for my error capturing: http://stackoverflow.com/questions/32437033/vba-excel-capturing-connection-errors – Sean Sep 07 '15 at 10:59