X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Refresh Each QueryTable Object in a Worksheet    
post Aug 26 2007, 05:56 PM

Posts: 5,055
Joined: 27-March 03
From: Minneapolis, MN, USA

Each worksheet with MS Query QueryTable Objects houses a collection of QueryTable Objects known as QueryTables, with a QueryTable being the Item Object within the Collection. Like most Objects within a collection, they can be referred to by Name or by Index number, e.g.,
Sub foo()
MsgBox Worksheets(1).QueryTables(1).Destination.Address(0, 0)
MsgBox Worksheets(1).QueryTables("MS Query1").Destination.Address(0, 0)
End Sub

Where the QueryTable Objects are housed in the first worksheet in the Workbook.
To refresh them all, you can use an Object Variable in a For/Next Loop to iterate through the Collection of QueryTable Objects in the Worksheet and Refresh them individually, e.g.,
Sub bar()
Dim qt As QueryTable, qts As QueryTables
'Pick a Worksheet for your QueryTable Objects
Set qts = Worksheets(1).QueryTables
'Loop through each QueryTable Object
For Each qt In qts
    'Refresh each QueryTable Object, _
        not in the BackGround
    qt.Refresh BackgroundQuery:=False
    'Not necesarry, just a demonstration
    MsgBox "Upper-left QueryTable Address: " & _
        qt.Destination.Address(0, 0)
'Terminate Collection Object
Set qts = Nothing
'We're done, let's look at the count of _
    QueryTable Objects in the Worksheet
MsgBox Worksheets(1).QueryTables.Count & _
    " QueryTable Objects Refreshed"
End Sub

My recomondation, when your goal is to refresh multiple QueryTable Objects in the same process, is to Refresh them one at a time and not in the Background. When you Refresh a QueryTable Object you establish a Connection to your Datasource (Database), and multiple QueryTable Objects require multiple Connections.
By forcing the QueryTable Object to Refresh in the Foreground you assure yourself that each QueryTable will be Refreshed and that you will never have more than one Active Connection to your Data Source at any given time.
Should there be questions or concerns, please follow up in our Excel forums. sad.gif
Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    15th December 2017 - 10:12 PM