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

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

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

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

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

Joel wrote:
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