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

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

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

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

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

"macroplay" wrote in message
...
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 ?

They're numbered in the order in which they were created beginning with
one. But in this situation you're probably safer giving them each explicit
names so you're sure you know which one you're operating on. Add the
following line of code to the procedure that creates the query table:

.Name = "Some_Unique_Name"

Then you can reference each query table by name, like so:

With Sheet1.QueryTables("Some_Unique_Name")

End With

--
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
...
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