CODEWITHSUNDEEP
excelvbarefresh
utkarsh
utkarsh

Reputation: 1

Refresh each excel sheet with 5-10 seconds

I have 100 sheets in my excel, and I can't run Refresh All, as it gives me error "[Microsoft] [ODBC Excel Driver] Too many client tasks." So currently I have to go to each sheet and click "Refresh" button manually after 5-10 seconds delay. How can I automate "Refresh" on all sheets by clicking one button with 5-10 seconds delay between each refresh?

Thanks in advance.

Upvotes: 0

Views: 305

Answers (1)

jblack
jblack

Reputation: 576

Try something like this:

Sub RefreshData()

    Dim ws As Worksheet
    Dim qt As QueryTable

    For Each ws In ThisWorkbook.Worksheets
        For Each qt In ws.QueryTables
            qt.Refresh BackgroundQuery = False
        Next
    Next

    MsgBox "Refresh complete"

End Sub

Alternate method:

Sub RefreshData()

    Dim cn As WorkbookConnection

    For Each cn In ThisWorkbook.Connections
        cn.Refresh
    Next

End Sub

Upvotes: 1

Related Questions