Proper use of XMLHTTP

Posted on 2008-11-04
Last Modified: 2008-11-05
I am writing some code in Excel VBA to access information on a piece of instrumentation. I have the following code written that sort of works by I need help with the xmlhttp.ReadyState portion.  If I check the ready state in the Do loop it never returns a 4. I put the msgbox in there to see what it was doing. On the first iterration it returns a 1. Once I click OK on the message box it exits the loop then returns a 4 and then returns the response text . I thought I was not giving it enough time to respond so I put in the counter. That did not work. Why does the msgbox trigger the correct ready state? How do I fix it?
Public Function Post_Data(ByVal dString As String, ByVal URL As String, ByVal NeedResponse As Boolean) As String


    Dim xmlhttp As Object

    Set xmlhttp = CreateObject("Microsoft.XMLHTTP")


    xmlhttp.Open "POST", URL, NeedResponse

    ' Indicate type of request being submitted (e.g. form data)

    xmlhttp.setRequestHeader "Content-Type", "application/x-www-form-   urlencoded"

    ' Indicate the header length

    xmlhttp.setRequestHeader "Content-Length", Len(dString) + 1

    MsgBox "Sending data string " & dString

    xmlhttp.Send (dString)


    Dim counter As Double


    Do While xmlhttp.ReadyState <> 4

        'MsgBox "Ready State A = " & xmlhttp.ReadyState

        counter = counter + 1

        If counter = 10000 Then Exit Do


    MsgBox "Counter = " & counter

    'MsgBox "Ready State B = " & xmlhttp.ReadyState

    'MsgBox "Status = " & xmlhttp.Status

    If xmlhttp.ReadyState = 4 Then

        If xmlhttp.Status = 200 Then

            Post_Data = xmlhttp.ResponseText

        ElseIf xmlhttp.Status <> 200 Then

            MsgBox "Server error: " & xmlhttp.Status

        End If

    ElseIf xmlhttp.ReadyState <> 4 Then

        MsgBox "Ready State error: " & xmlhttp.ReadyState

    End If

End Function

Open in new window

Question by:pwken
    LVL 50

    Accepted Solution

    Why do you have a counter - I'm guessin that its exiting prematurely

    Inside your Do loop you could use

    a) nothing
    b) DoEvents
    c) Sleep (either s an API or as part of Windows Script Host)



    Author Comment

    I put the counter in to attempt to see what was going on within the loop. I also thought of it as a safety net in the event that the ready state never reached 4.

    The DoEvents works and solves my original question so points will be awarded; however, this leads me to another question. How to handle the Do loop in the event that the response state never reaches 4?
    LVL 50

    Expert Comment

    by:Dave Brett

    I've never seen it not get achieve readystate.

    But you would need to use a timer, or counter to exit the loop if that was of concern



    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
    Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
    The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
    This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now