Nick thank you for your help.
I realy dont understand this stuff. I will have to get my programming friend
to read your messages. He will understand if we can use your suggestions.
You have been very helpful to me. Thank you NickHK,
John.
--
A Pint of Guinness a day keeps the doctor away.
"NickHK" wrote:
Here's one way to test the server yourself before you let Excel work. Then
you can control which file(s) you attempt to open.
This is done synchronously here, so you will have to wait for success or the
time out period for each file you test.
Changing to asynchronous and events will speed things up, but you then have
keep track of which have succeded and which failed, when each called
returns.
You will need to set a check against the reference "Microsoft WinHTTP
Services", ToolsReferences and scoll down until you see it.
Private Sub CommandButton1_Click()
Dim Stats_Book As Workbook
Dim HTTPObj As WinHttpRequest
Const URLStr As String = <YourURL
'Some suitable value
Const TIMEOUT As Long = 10 'Seconds
Set HTTPObj = New WinHttpRequest
With HTTPObj
.Open "GET", URLStr, True
.Send
If .WaitForResponse(TIMEOUT) = False Then
MsgBox "Timed out for: " & URLStr
Else
MsgBox "OK"
Set Stats_Book = Application.Workbooks.OpenXML(URLStr) ',
LoadOption:=xlXmlLoadOpenXml)
End If
End With
End Sub
NickHK
"John37309" wrote in message
...
Nick, i thank you very much for helping with this as i dont realy have a
clue
about how to program this stuff. I have a very basic test doc built that
you
could try to install your code on for me if you want.
http://www.paddysinspace.com/download.php?id=15
The test doc is just one button that makes a call for xml data on a server
that always gives me this problem. I guess you would just have to trust
that
i am genuine about trying to solve this problem.
If you can get it to work, you would have to post the working doc on the
web for me to download the working version or i will give you my e-mail
address to mail it.
I will try to put in your code in the doc myself to see if i can do it.
I'm
more the manager than the programmer..LOL
Let me know if you can help me further Nick.
Thank you, John.
--
A Pint of Guinness a day keeps the doctor away.
"NickHK" wrote:
Not sure if this will help with your errors, as I cannot test it, but
may be
something like:
'<Worksheet code
Const TIMEOUT As Long = 30 'seconds
'
'Other query code
'
Set WhichSheet =Me
Application.OnTime TimeSerial(Hour(Now()), Minute(Now()), Second(Now) +
TimeOut), "TestConnections"
'</Worksheet code
'<Module code
Public WhichSheet As Worksheet
Public Sub TestConnections()
Dim i As Long
With WhichSheet.QueryTables
For i = 1 To .Count
If .Item(i).Refreshing Then
.Item(i).CancelRefresh
Debug.Print "Failed to refresh in time: " &
..Item(i).Connection
Else
Debug.Print "Refreshed in time: " & .Item(i).Connection
End If
Next
End With
End Sub
'</Module code
NickHK
"John37309" wrote in message
...
I am using MS Excel to import XML data off the web using macro's. When
i
hit
a server to get data, sometimes my document "Hangs" trying to get the
data.
It happens when a server is not functioning properly and Excel makes
the
connection but never gets the full page of data. It happens on servers
that
are there but not functioning properly any more or something.
The problem is the same as when you use a normal web browser and click
on
a
web page that never fully up-loads to your computer (Some old
website).
What
i do when that happens is i just close the window. MS Excel is not
doing
this, it just keeps trying.
The result is that my document just comes to a compleet stand still
waiting
for a page that it is not going to get. The document i am using has
been
programed to make 30 or 40 calls for data off different web servers
before
it
finishes.
What i need is to be able to set some kind of "Time out" value in the
Visual
Basic Editor so if it does not get the data in , lets say 30 seconds,
then
it
see's it as an error and stops trying and just moves on to make the
next
request for data.
Any help here would be appreciated.
Thank you.
John.
--
A Pint of Guinness a day keeps the doctor away.