Excel 2003 download

[Cotton9]

Attempting to download a S & P stock market graph. This snippet works under Excel 2007 but fails under 2003. I have tried many variations from this web site and others with no closer solution.

oXML.Send line fails with status code of 2029

Sub dbins()


    Dim cText As String
    Dim oXML     As Object
    Dim oResp
    Dim vLocalFile as string
    Dim cURL    As String
    Dim lRetImmed As Boolean    ' True = Return immediently, False = wait
    
    vLocalFile = "./S_PGraph.png"

    cURL = "[URL unfurl="true"]http://app.quotemedia.com/quotetools"[/URL] & _
            "/getChart?webmasterId=90699&symbol=^SP500&" & _
            "chscale=3m&quoteBox=true&chln=1A5488&chfill=" & _
            "D6E7F2&chfill2=D6E7F2&chtype=AreaChart&chwid=" & _
            "390&chhig=250&chpccol=FF4C00&chfrmon=" & _
            "false&chton=false&chpcon=true"
    
    '   IE status Property Value & Corresponding statusText Value
    '   12002   ERROR_INTERNET_TIMEOUT
    '   12007   ERROR_INTERNET_NAME_NOT_RESOLVED
    '   12029   ERROR_INTERNET_CANNOT_CONNECT
    '   12030   ERROR_INTERNET_CONNECTION_ABORTED
    '   12031   ERROR_INTERNET_CONNECTION_RESET
    '   12152   ERROR_HTTP_INVALID_SERVER_RESPONSE



    Set oXML = CreateObject("MSXML2.XMLHTTP")

    If oXML Is Nothing Then
        MsgBox "For some reason I wasn't able to make a MSXML2.XMLHTTP object"
        Exit Sub
    End If
        
    oXML.Open "GET", cURL, False

                ' Send fails and code of 2029
    oXML.Send

        ' Wait for request to finish
    Do While oXML.readyState <> 4
        DoEvents
    Loop
     
    oResp = oXML.responseBody  ' Returns the results as a byte array (not very useful)
  
        ' Create local file and save results to it
    vFF = FreeFile
    
    If Dir(vLocalFile) <> "" Then Kill vLocalFile
    Open vLocalFile For Binary As #vFF
    Put #vFF, , oResp
    Close #vFF
     
        ' Clear memory
    Set oXML = Nothing

End Sub

Thanks,


D. Buckman
US Army Corps of Engineers, Omaha

Learn from the past, Live in the present, Create the future

 

[Welshbird]

Check the references to see if you are now missing a reference to the correct XML library. IF you don't - it will be marked as missing.

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[SkipVought]

hi,

works under Excel 2007 but fails under 2003.

Always code in the lowest version that the application will be run in. Test in all.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Cotton9]

Where would the XML Library be found and how to reference it in my code?
Learning VBA as I go along and learning from others code. Nothing fancy just 'good enough'.
Any help is good to learn from.

D. Buckman
US Army Corps of Engineers, Omaha

Learn from the past, Live in the present, Create the future

 

[Cotton9]

Under VBA Tools/Reference tried each of the Microsoft XML, Version V2.0 - V6.0 options but did not help. Obviously missing something or two!

D. Buckman
US Army Corps of Engineers, Omaha

Learn from the past, Live in the present, Create the future

 

[Cotton9]

Ran against a local server and the codes works but against the S & P web site it fails at the .send statement. The MS error message returned is "The system cannot locate the resource specified" with run-time error '-2146697211'. The oXML.stat returns 12029, 'unknown'.

Sub dbins()
'
'
' Test IE download of graph via XML
'

    Dim cText As String
    Dim oResp
    Dim cURL    As String


    Dim oXML     As Object
    Set oXML = CreateObject("MSXML2.XMLHTTP")
    If oXML Is Nothing Then
        MsgBox "For some reason I wasn't able to make a MSXML2.XMLHTTP object"
        Exit Sub
    End If


	' url to S & P three month daily stock market graphic
          ' even if displayed in IE first
    cURL = "[URL unfurl="true"]http://app.quotemedia.com/quotetools"[/URL] & _
            "/getChart?webmasterId=90699&symbol=^SP500&" & _
            "chscale=3m&quoteBox=true&chln=1A5488&chfill=" & _
            "D6E7F2&chfill2=D6E7F2&chtype=AreaChart&chwid=" & _
            "390&chhig=250&chpccol=FF4C00&chfrmon=" & _
            "false&chton=false&chpcon=true"

	' this url works if displayed in IE first
'    cURL = "[URL unfurl="true"]http://www.nwo.usace.army.mil/../"[/URL] & _
            "NWO-corpsbanner.jpg"


    oXML.Open "GET", cURL, False

            ' fails under Excel 2003 and S & P url
    [b]oXML.Send[/b]	' The .send fails when S & P url is used
                        ' but works with test url

        ' Wait for request to finish
    Do While oXML.readyState <> 4
        DoEvents
    Loop
     
    oResp = oXML.responseBody 


        ' Create local file and save results to it
    vFF = FreeFile
    
    If Dir(vLocalFile) <> "" Then Kill vLocalFile
    Open vLocalFile For Binary As #vFF
    Put #vFF, , oResp
    Close #vFF
     
        ' Clear memory
    Set oXML = Nothing

End Sub

So the question now is why one URL works and the other does not? Both can be displayed in IE.

Any ideas?

Thanks,


D. Buckman
US Army Corps of Engineers, Omaha

Learn from the past, Live in the present, Create the future