Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Inport External Data - new queries | Excel Worksheet Functions | |||
Excel 2002 External Data Queries and UNC !HELP! | Excel Discussion (Misc queries) | |||
External Data Queries - Data Range Properties v Spreadsheet Format | Excel Discussion (Misc queries) | |||
Excel 2003 This Workbook contains queries to external data | Excel Discussion (Misc queries) | |||
How do I pull multiple queries of external data from the Internet into Excel? | Excel Programming |