Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Proper use of XMLHTTP

Posted on 2008-11-04
Medium Priority
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
  • 2
LVL 50

Accepted Solution

Dave Brett earned 2000 total points
ID: 22880685
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

ID: 22885368
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
ID: 22885527

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



Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

571 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