External data queries within applcation collection

  • Thread starter Thread starter Andy S
  • Start date Start date
A

Andy S

Hi,

I have a workbook with many sheets and approx. 10 of these sheets
query data from an Acc97 db. I have some vba code that executes a
'RefreshAll', but this doesn't always seem to work ?????

I'd like to cycle through each one seperately and assume there is a
reference to each query in the Application collection but I can't find
it.

If it exists I assume I can set up a loop, based on the number of
queries in the workbook and refresh each one individually? which I
think means I won't need to make any changes to this code if the
number of queries change ?

A link to a website explaining this, or an appropriate keyword to
enter in to Excel help would be great.

Many thanks in advance of a helpful reply,
 
The following code refreshes each query in the active workbook:

Sub MyQueries()
Dim qry As QueryTable
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
For Each qry In ws.QueryTables
qry.Refresh
Next qry
Next ws
End Sub
 
Back
Top