Refreshing Data Query

[Todd Huttenstine]

What is the code to refresh a database query? I have a
database query located in columns AD:AJ.

Below is what I got when I did a macro. Just want to make
sure this is the proper way to programmaticaly do it. I
selected a random cell that contains part of query and
then right clicked and hit refresh while running the
macro. The code is below...

Range("AD335").Select
Selection.QueryTable.Refresh BackgroundQuery:=False


Are there other better ways to do it?

Thank you

Todd Huttenstine

 

[Dave R.]

Interesting. I recorded this in XL 2000, and it gave me this which
refreshes data from an Access database query. It refreshes only the query
I'm in, not all the queries in the workbook.. and I only have one query per
sheet.

Sheets("Baseline").Select
With ActiveSheet.QueryTables(1)
.BackgroundQuery = False
.Refresh
End With

 

[Todd Huttenstine]

I only have 1 data query on my sheet as well. If I do
that does the current selection need to be one of the
cells that the data query is in? The reason I ask that is
because I know that in order to refresh one, you must have
one of the cells selected or you will not even get an
option to refresh the data query.


Thank you

Todd

 

[Dave R.]

No. But try it and see to be sure. I have been using that for months with no
problems.

Note that the availability of a menu option doesn't necessarily mean that
option cannot be done in other ways.

 

[Don Guillett]

Todd, I just tested this from sheet2 for a stock market query to Yahoo.
xl2002

Sub refreshremote()
Sheets("sheet1").QueryTables(1).Refresh
End Sub

 

[Dick Kusleika]

Todd (and Dave)

No, you don't have to select the range in order to refresh (although you do
in the user interface). The most direct approach is

Sheet1.QueryTables(1).Refresh

or

Sheet1.QueryTables("Query from Access").Refresh

Change the sheet and querytable names to suit your situation.

If you want to refresh all of the querytables and external data pivot tables
in a particular workbook, there is a RefreshAll method of the Workbook
object

ActiveWorkbook.RefreshAll