I'm putting together a spreadsheet that contains multiple ODBC remote data
connections and what I'm trying to do is set up a prompt that refreshes the
data in the spreadsheet properly. The problem is that one of the remote
connections is actually a stored procedure that updates some info on a SQL
server that needs to be run before one of the other queries refreshes.

So my quesion is if there are VB commands to refresh single connections at a
time, so I can make sure they refresh in order.

I tried this code but got an error message:
Range("Data!BH4").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False

The error I get is "Run-time error '1004': Select method of Range class
failed".

I get the same error if I try to replace the "Data!BH$" part with the actual
name of the data range as well.

Just wondering if there's an easier way to do this.

hi
guessing here but i don't think you need the ListObjects in your code line
to refresh a ODBC querytable. ListObjects was introduced in xl03. i have read
a little on it (ie what's new) but haven't had a chance or need to work with
it yet.
see this site
http://msdn.microsoft.com/en-us/libr...ffice.11).aspx
try the refresh with out the ListObject keyword.

Regards
FSt1

"thefonz37" wrote:

I'm putting together a spreadsheet that contains multiple ODBC remote data
connections and what I'm trying to do is set up a prompt that refreshes the
data in the spreadsheet properly. The problem is that one of the remote
connections is actually a stored procedure that updates some info on a SQL
server that needs to be run before one of the other queries refreshes.

So my quesion is if there are VB commands to refresh single connections at a
time, so I can make sure they refresh in order.

I tried this code but got an error message:
Range("Data!BH4").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False

The error I get is "Run-time error '1004': Select method of Range class
failed".

I get the same error if I try to replace the "Data!BH$" part with the actual
name of the data range as well.

Just wondering if there's an easier way to do this.

hi
After looking at it again i think you have another error in the select if
you are trying to switch sheets.
try
sheets("Data").range("BH4").select
in fact, you really don't need to select.

sheets("Data").range("BH4").QueryTable.Refresh BackgroundQuery:=False

regards
FSt1

"FSt1" wrote:

hi
guessing here but i don't think you need the ListObjects in your code line
to refresh a ODBC querytable. ListObjects was introduced in xl03. i have read
a little on it (ie what's new) but haven't had a chance or need to work with
it yet.
see this site
http://msdn.microsoft.com/en-us/libr...ffice.11).aspx
try the refresh with out the ListObject keyword.

Regards
FSt1

"thefonz37" wrote:

I'm putting together a spreadsheet that contains multiple ODBC remote data
connections and what I'm trying to do is set up a prompt that refreshes the
data in the spreadsheet properly. The problem is that one of the remote
connections is actually a stored procedure that updates some info on a SQL
server that needs to be run before one of the other queries refreshes.

So my quesion is if there are VB commands to refresh single connections at a
time, so I can make sure they refresh in order.

I tried this code but got an error message:
Range("Data!BH4").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False

The error I get is "Run-time error '1004': Select method of Range class
failed".

I get the same error if I try to replace the "Data!BH$" part with the actual
name of the data range as well.

Just wondering if there's an easier way to do this.