Import data "Hangs", A "time out" is needed. Help! :)

[Guest]

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.

 

[NickHK]

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

 

[Guest]

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.

 

[Guest]

Nick to add to my last message. I'm not sure where to put in the XML url for
getting my data. I'm not good at this.
In my test doc, this is the code in the visual basic editor.
............................................
Sub bebeer()
Set stats_book =
Application.Workbooks.OpenXML("http://hashbreaker.com:8700/tmrldrtg/get_project_config.php", LoadOption:=xlXmlLoadOpenXml)
End Sub
.........................
Nick it is just a small test code. Will you put in my url into YOUR code for
me! I dont know where to put it in.

I'm sorry about this. I am not good with this stuff.

John.

 

[NickHK]

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", Tools>References 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

 

[Guest]

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.