XMLHTTP: system cannot locate the resource specified

[iknowu99]

hello all,

i am getting the error
run-time error '-2146697211 *800c0005)'':
the system cannot locate the resource specified

when running the following:
my guess is because sometimes the internet connection doesnt always work, or the page loads too slow, but 'on error resume next' doesnt work because the line takes awhile to execute and then excel turns off

i believe i saw somewhere here (mrexcel) how to give specific commands a limited time to run. i dont rememeber how to do this though, or any other workaround >> help is appreciated

Sub foobar()
Set xmlhttp = CreateObject("Microsoft.XMLHTTP")
strURL = "http://www.yahoo.com"
xmlhttp.Open "GET", strURL, False, "", ""
xmlhttp.Send 'freezes here often
RtnPage = xmlhttp.ResponseText

Set fs = CreateObject("Scripting.FileSystemObject")
Set ag = fs.CreateTextFile("C:\test.html", True)
ag.Writeline (RtnPage)
ag.Close
End Sub

 

[iknowu99]

bump

 

[iknowu99]

bump

 

[Norie]

What exactly are you trying to do?

I get that error whenever I try to run that code, and I think the reason On Error... doesn't work is because the error isn't really being generated by Excel.

 

[iknowu99]

Hi Norie

I would like to place website information to a sheet. I have done some experimenting with this in the past and I remember that the XML method was significantly faster than queries ( that was with excel 2003 though not 2007 which i use now so the goal for this instance is to take yahoo.com page and place it to sheet1. I do this with

Sheets("Sheet1").QueryTables(1).Refresh BackgroundQuery:=False

where sheet1 was preprogrammed to query from C:\test.html.
It might seem like a loop but the whole goal is to download information from online without worrying about internet connection. since right now i'm using wireless

let me know, thanks, best

 

[xenou]

Maybe this example will help? We have at least some kind of attempt here to handle a slow connection...
http://www.hostedscript.com/site/en/Nyheder/default/show/2

 

[iknowu99]

Hi Alexander Barnes

I will try this method right now. Just from looking at it though, it doesnt make sense to me because the line that freezes, Call HTTP.Send , is before the while wending loop. *testing now*


also it seems related to some of the links i have found:

And as of now I have been trying Tushar's method with classes which is not working for me, but then read about jkpieterse's method of msxml.DOMDocument which seems optimal

i will post the links here; although i dont understand them to the end, i plan to get this stuff working:

http://www.tushar-mehta.com/publish_train/xl_vba_cases/vba_web_pages_services/index.htm

http://www.dailydoseofexcel.com/archives/2007/08/03/vba-and-internetexplorer-and-xmlhttp/

 

[xenou]

Hmmm, this all works perfectly for me. I agree - it gets a little detailed as you dig into it - too much for me right now Do you have problems with internet connections otherwise? Perhaps its really a problem occurring with the Open Method not having successfully completed the task? Interesting ...

Alex

 

[iknowu99]

so far I had to change:

If HTTP.readyState = 4 And HTTP.status = 200 Then

to

If HTTP.readyState = 4 Then

because the second part generates an unknown error when first part = 3

 

[iknowu99]

Option Explicit

Sub foobar()
Dim timeout As Single
Dim xmlhttp As Object
Dim strURL As String
Dim RtnPage As String
Dim fs As Object, ag As Object

Set xmlhttp = CreateObject("Microsoft.XMLHTTP")
strURL = "http://www.yahoo.com"
xmlhttp.Open "GET", strURL, False, "", ""
xmlhttp.Send 'freezes here often. maybe because of poor connection or worse, no connection




'still not sure whether this while/wend loop helps timeouts since it is after the freeze/timeout problem of 'xmlhttp.Send'
timeout = Timer + 3
While xmlhttp.readyState <> 4 And timeout > Timer
    DoEvents
Wend
'still not sure whether this while/wend loop helps timeouts since it is after the freeze/timeout problem of 'xmlhttp.Send'
'still not sure whether this while/wend loop helps timeouts since it is after the freeze/timeout problem of 'xmlhttp.Send'




If xmlhttp.readyState = 4 Then  'And HTTP.status = 200 Then
    RtnPage = xmlhttp.ResponseText
    
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set ag = fs.CreateTextFile("C:\test.html", True)
    ag.Writeline (RtnPage)
    ag.Close
End If
End Sub