External data queries within applcation collection
 

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,

External data queries within applcation collection
 

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


Andy S wrote:
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,


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

External data queries within applcation collection
 

Debra,

Thanks for a perfect example of good code.....



Debra Dalgleish wrote in message ...
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


Andy S wrote:
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,