Disable Background Refresh for External Data VBA

[rupertlo]

Hi there, I have searched the forum and apologise if this question has been asked before (which I am sure it has).

I have an Excel spreadsheet with some VBA which pulls stock data from Yahoo finance, via a macro.

Even when I am not running the macro to fetch the data, the worksheet is incredibly slow. It recalculates very quickly (it is a small worksheet) but moving from, say, cell A1 to A2 takes about five seconds rather than being instantaneous.

Is there a way to disable background query refresh for all external data connections in VBA. I would plan to stick in some script at the start of my VBA to enable background refresh and then switch it off at the end of the macro.

Many thanks,

Rupert.

 

[rupertlo]

.

 

[cr731]

.

?

 

[John_w]

Is there a way to disable background query refresh for all external data connections in VBA. I would plan to stick in some script at the start of my VBA to enable background refresh and then switch it off at the end of the macro.

Public Sub Disable_Background_Refresh()

    Dim ws As Worksheet
    Dim qt As QueryTable

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

and True to enable background queries.