Sorry to ask another question...

In your example code

With Sheet1.QueryTables(1)

If I have done multiple QueryTables.Add on the same sheet how are they
numbered. Is the first qyuery 1 or 0 and do they just increment ?

thanks

"Rob Bovey" wrote:

Is there a way to bring web data into a worksheet without the refresh
feature like I am attempting to do now :

A query table will accept a new connection string at any time. Assuming
you can automatically load your URL string variable with the correct URL
required for each refresh, you'd create the initial query table as before
but without setting the RefreshPeriod property, then use the following code
in a loop to update the query table with the new data from each new URL:

URL = "http://www.NextURL...."
With Sheet1.QueryTables(1)
.Connection = "URL;" & URL
.Refresh False
End With

There's no need to clear the previous data, the query table will replace its
previous data with the data from the new URL, even if the height and width
of the ranges are different.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

"macroplay" wrote in message
...
Yes, I am running that code every 5 minutes.

Using the refresh feature would be problematic though because I have to
access a few different web sites and the URLs change every day because
they
include a date string (I only posted one example of the web acces code).

Is there a way to bring web data into a worksheet without the refresh
feature like I am attempting to do now :

loop
set URL (which changes all the time)
get data into 1 and only temporary worksheet (temporary worksheet data
is
cleared first)
parse data and copy into a permanent worksheet
end loop

"Rob Bovey" wrote:

Hi macroplay,

Are you repeatedly running the entire section of code you've shown?
If
so you're creating lots and lost of query tables, which may be your
problem.
You can create the query table once, give it a refresh interval and it
will
automatically update itself without any problems. Here's an example based
on
your code:

URL = "http://www.federalreserve.gov/releases/h10/Hist/dat00_ca.txt"
With ActiveWorkbook.Worksheets("Fx").QueryTables.Add( _
Connection:="URL;" & URL, Destination:=Worksheets("Fx").Cells(1, 1))
.BackgroundQuery = True
.Refresh False
.RefreshPeriod = 60
End With

Just run this one time and it will create a query table that
automatically
refreshes itself every 60 minutes (you can change the RefreshPeriod
property
value to get a different refresh interval).

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

"macroplay" wrote in message
...
I have a excel macro that continually gets data from the web. It will
run
fine for a while and then the entire network connection from the PC
hangs.
I
can't access any network drives or the web. If I go into DOS and type
"netstat" all the connects are in "Close_Wait" state.

As soon as I close Excel the network works again.

The code I am using to get data from the web is :

URL = "http://www.federalreserve.gov/releases/h10/Hist/dat00_ca.txt"
With ActiveWorkbook.Worksheets("Fx").QueryTables.Add( _
Connection:="URL;" & URL, Destination:=Worksheets("Fx").Cells(1, 1))
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With



If anyone has any idea what could be happenign and how to fix this
please,
please let me know.

thanks