How do you refresh SQL data in Excel with VBA?

[nmay732]

I have a correctly connected query table on a worksheet called "SQL Data" that I can refresh manually no problem.
However, when I run this VBA code I get the following error:

Dim ws As Worksheet
Set ws = Worksheets("SQL Data")

'Refresh the SQL Table
ws.QueryTables(1).Refresh

>>ERROR: Subscript out of range (On "ws.QueryTables(1).Refresh")

On further inspection using the Locals pane, I found the count of the object QueryTables for ws is 0

Why is the table not being added into QueryTables?
Is there another way to refresh the data with VBA?

 

[Fazza]

maybe simplest is thisworkbook.refreshall

note refreshes pivot tables & query tables

otherwise loop through each wks and through each qt and refresh one at a time - using for each to loop the collections

hth

 

[nmay732]

ActiveWorkbook.RefreshAll worked beautifully.

Thanks so much for your help!