HELP! Network Hung

[Guest]

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

 

[Rob Bovey]

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

 

[Guest]

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]

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

 

[Guest]

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]

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

 

[Guest]

I must have something wrong. Using the following test code, the first
query.add actually gets the web data. The second query("Fx") gives a debug
error "subscript out of range". If I replace the "Fx" with 1 then no data is
retrived from the web. The ".refresh false" gives a "object required" debug
error.

============ code >

Dim URL As String

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

MsgBox "continue"

Cells.Select
Selection.ClearContents
Range("a1").Select

URL = "http://www.federalreserve.gov/releases/h10/Hist/dat00_ca.txt"
With ActiveWorkbook.Worksheets("Sheet1").QueryTables("Fx") _
.Connection = "URL;" & URL
' refresh false
End With

===============================

 

[Guest]

I also tried the following code but the 2nd query returned data from the old
URL.

============= code >

Dim URL As String

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

MsgBox "continue"

Cells.Select
Selection.ClearContents
Range("a1").Select

URL = "http://mis.nyiso.com/public/csv/rtlbmp/" & sdate & "rtlbmp_zone.csv"
With ActiveWorkbook.Worksheets("Sheet1").QueryTables(1) _
.Connection = "URL;" & URL
End With

ActiveWorkbook.Worksheets("Sheet1").QueryTables(1).Refresh

=========== end code

 

[Rob Bovey]

I must have something wrong. Using the following test code, the first
query.add actually gets the web data. The second query("Fx") gives a debug
error "subscript out of range". If I replace the "Fx" with 1 then no data
is
retrived from the web. The ".refresh false" gives a "object required"
debug
error.

The subscript out of range error is probably the result of having
created multiple query tables. Choose Insert/Name/Define with Sheet1 active
and you'll probably see names like Fx_1, Fx_2, etc. Try copying the code
into a brand new workbook and try again.

If you have to create the same query table more than once you first need
to delete its associated range name, delete the query table itself from VBA
with QueryTables(x).Delete, then save the workbook, close it and re-open it.
Otherwise Excel won't let you use the same unique name again. Unfortunately,
it doesn't throw an error when you try, it just creates a different name by
appending an underscore and a number to the name you're trying to use. This
makes it a a very tricky bug.

The second problem is caused by syntax errors in your With statement.
There should be no line continuation character on the first line and the
Refresh method needs a dot operator in front of it. The corrected code looks
like this:

With ActiveWorkbook.Worksheets("Sheet1").QueryTables("Fx")
.Connection = "URL;" & URL
.Refresh False
End With

In a brand new workbook with the above fix your code runs fine for me.

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

 

[Rob Bovey]

I also tried the following code but the 2nd query returned data from the
old

Paste the following code into a module in a new workbook and you should
see it return two data sets corresponding to the two different URLs.

Sub WebQueryTest()

Dim URL As String

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

MsgBox "Data set one"

URL = "http://www.federalreserve.gov/releases/h10/summary/indexb_m.txt"
With ActiveWorkbook.Worksheets("Sheet1").QueryTables("Fx")
.Connection = "URL;" & URL
.Refresh False
End With

MsgBox "Data set two"

End Sub

Keep in mind that this is just for demo purposes. The code that modifies
the connection and refreshes the query table would normally need to be in a
separate procedure from the one that initially creates the query table.

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

 

[Guest]

Thanks for your help Rob. I'm going to leave my macro running overnihgt to
see if this helped.

john

 

[Guest]

Rob, I hope you're still listening...

The fix didn't help. the Macro ran for about 13 hours then the network
interface hung. To speed up coding the fix all I did was delete all the
defined names and querytables at the end of every loop of the macro instead
of reusing querytables. I assumed this would have the same effect.

Would you have any other ideas to try ?

Also let me know if email is preferred.

thanks,

john

 

[Rob Bovey]

The fix didn't help. the Macro ran for about 13 hours then the network
interface hung. To speed up coding the fix all I did was delete all the
defined names and querytables at the end of every loop of the macro
instead
of reusing querytables. I assumed this would have the same effect.

13 hours? How many of these are you running? It's quite possible there's
some kind of minor resource leak going on that wouldn't normally cause
problems until you run query tables for very long periods of time. My best
advice would be to break this up into smaller batches and restart Excel in
between.

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

 

[Guest]

The macro runs every 5 minutes from an excel timer trigger. Every 5minute run
the macro does about 40 or more qureytables out to various web pages. At the
end of the run it starts the 5 minute excel timer again. So basically the
macro runs endlessly (or I want it to) every 5 minutes.

I guess I'll have to consider your advice.

Thanks,

john

 

[NickHK]

At a guess, 40 query tables every 5 minutes = 7.5 seconds/QT.
If a site is slow to answer and/or depending on the production of your final
web pages, the timer could trying to fire again before all your refreshes
are done, if you have not disabled the before.

Also, you do know that MS does not recommend/support Excel (or Office
application) running in such an environment, as they were/are not designed
for continuous use.
Rob's suggestion of Start/Close Excel at some regular interval may clear it.

But your have these kind of requirements, Excel may not be the best tool.

NickHK

 

[Guest]

Yes, I'm redesigning the code to start-stop.

As a side note I had some of the excel sheets publishing to the IIS server
on my local C drive and republishing every 5minutes when the files got saved.
When I removed these the system has been running for 24 hours. I think
there's still some memory leaks so I'll continue with Rob's suggestion.

john