Excel VBA to loop through all connections and refresh

Hi all

What's the VBA object for 'all connections' (bold below), and can it be used to iterate through all connections and refresh?   I have some VBA formatting going on, and I'd like to have one command button that users can click to both refresh connections and do the formatting.

Thanks.
Jim


'Refresh all connections
Dim cn As WorkbookConnection
Dim ocn As OLEDBConnection

For Each cn In Workbook.Connections
    Set ocn = cn.OLEDBConnection
    With ocn
        .BackgroundQuery = False
        .Refresh
    End With
Next
LVL 66
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAsked:
Who is Participating?
 
Saurabh Singh TeotiaCommented:
Jim,

Rather doing this way..you can do this way..

ActiveWorkbook.RefreshAll

Open in new window


This will refresh all your background query in your workbook...

Saurabh...
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
Looks like a winner.  Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.