Auto Refresh Query Table

[Scotty9349]

I have a MS Query to retrieve data from an SQL server database through ODBC
Connection. Lets call this Spreadsheet 1.

I created a separate spreadsheet (Spreadsheet 2) that has a pull down menu
which links to Spreadsheet 1. When I use the link, Spreadsheet 1 does not
enable Automatic refresh. However, if I go directly to Spreadsheet 1, it will
prompt for refresh upon open.

Spreadsheet 2 is my personal spreadsheet, whereas Spreadsheet 1 is used by a
group of others.

Is it possible to write some code to refresh the MS Query when I open
Spreadsheet 1 from Spreadsheet 2?

Any insight would be greatly appreciated.

Thanks.
Scotty9349

 

[Joel]

Try code like this

Sub refresh()

Set sht = ThisWorkbook.Sheets("sheet6")

For Each query In sht.QueryTables
query.refresh
Next query

End Sub

 

[dan dungan]

Hi Joel,

I'm using excel 2000 and this code fails on sht and query

with the following message "Compile error: Variable not defined"

So then, I revised the code and it fails with the message:
"Run-time error '9': Subscript out of Range"

Dim sht As Worksheet
Dim query As QueryTable
Set sht = ThisWorkbook.Sheets("sheet16")

For Each query In sht.QueryTables
query.Refresh
Next query

Do you have any recommendations?

Thanks,

Dan

 

[Joel]

I guess you have option explicit set so you are required to declare
variables. I also assume you have a query table on sheet16. You didn't
specify which line the error occured. Sheet16 should exactly match the name

 

[dan dungan]

Thanks Joel.

I was trying to say that "query" was highlighted on the line
For Each query In sht.QueryTables

But I changed "sheet 16" to "Customers"--the name
on the Tab at the bottom of the worksheet--as you recommended, and
the macro runs without error.

Thanks,

Dan