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
on the Tab at the bottom of the worksheet. There should be no spaces at the
beginning or end of the worksheet name.

"dan dungan" wrote:

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 wrote:
Try code like this

Sub refresh()

Set sht = ThisWorkbook.Sheets("sheet6")

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

End Sub

"Scotty9349" wrote:

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