Excel VBA Msxml2.XMLHTTP.6.0 vs Msxml2.ServerXMLHTTP.6.0

excelvbawindows 7

I am a self-taught, amateur programmer, and I am new to this forum. Please bear with me.

About two years ago, I wrote a simple Excel vba program to login in to a website and grab a customer statement in the form of a .csv file. My program utilizes GET and POST requests. This program worked perfectly (for my needs) until about three weeks ago, when it unfortunately broke on me. The program could not get through the initial GET request. Specifically, it would break on the getReq.send line.

I came across this post:
Login into website using MSXML2.XMLHTTP instead of InternetExplorer.Application with VBA

Here, I learned that you can use "Msxml2.XMLHTTP.6.0" instead of "Msxml2.ServerXMLHTTP.6.0". I modified my code accordingly, eliminating the need to parse cookies after the Get request, and it worked! But I have no idea. Even though I got it to work, I do not feel like I have learned much in the process.

Some information to note:

  • My original program broke on my work computer (WindowsXP).
  • Figuring that it may be an XP issue, and in the market for a new machine anyway, I updated to a new computer running Windows7. The program still did not work, though I received a different error message.
  • I ran my code on a Windows10 computer and it worked fine.
  • I use identical code to connect to various other websites and it works fine, regardless of what operating system.

So, my specific questions:

  1. Why might the code work with Msxml2.XMLHTTP.6.0 but not Msxml2.ServerXMLHTTP.6.0?
  2. And why might the code have broken in the first place?
  3. Why would the code work on one particular website, but no another?

Any insight would be greatly appreciated. I have attached my code (with login info X'd out).

Sub RCGInquiry()

    Dim postReq, getReq, cookies
    Dim p0 As Integer, p1 As Integer, temp As String
    Dim result As String, respHead As String

    Set getReq = CreateObject("Msxml2.ServerXMLHTTP.6.0")
    'Set getReq = CreateObject("Msxml2.XMLHTTP.6.0")

    ' Visit homepage so we can find the cookies
    getReq.Open "GET", "https://www.rcginquiry.com/sfs/Entry", False
    getReq.send

    respHead = getReq.getAllResponseHeaders

     Debug.Print respHead

    ' Need to parse the cookie from Respone Headers
    cookies = ""
    p0 = 1
    Do While InStr(p0, respHead, "Set-Cookie:") > 0
        p0 = InStr(p0, respHead, "Set-Cookie:") + 11
        p1 = InStr(p0, respHead, Chr(10))
        temp = Trim(Mid(respHead, p0, p1 - p0))
        cookies = cookies & temp & "; "
    Loop
    cookies = Left(cookies, Len(cookies) - 2)

    ' Debug.Print cookies

    ' Login
    Set postReq = CreateObject("Msxml2.ServerXMLHTTP.6.0")
    'Set postReq = CreateObject("Msxml2.XMLHTTP.6.0")
    postReq.Open "POST", "https://www.rcginquiry.com/sfs/Entry", False
    postReq.setRequestHeader "Cookie", cookies
    postReq.setRequestHeader "Content-type", "application/x-www-form-urlencoded" 'send appropriate Headers
    postReq.send "Usrid=XXXX&Psswd=XXXX" ' send login info

    '-------------------------------------------------------------------------------

    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim FSO As Object
    Dim myFile As Object
    Dim path As String
    Dim y As Integer

    curDate = Format(Date, "mm_dd_yy")

    ' Download CSV
    postReq.Open "POST", "https://www.rcginquiry.com/sfs/Downloads/tmp.csv?filetype=POS&format=MFA20&heading=true&allaccts=true&junk=tmp.csv", False
    postReq.setRequestHeader "Cookie", cookies 'must resend cookies so it knows i am logged in
    postReq.setRequestHeader "Content-type", "application/x-www-form-urlencoded"
    postReq.send "filetype=POS&format=MFA20&heading=true&allaccts=true&junk=temp.csv" 'url query parameters

    ' Writes responseText to a .csv file
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set myFile = FSO.createtextfile("C:\Users\Adam\Desktop\POSITION\" & curDate & ".csv", True)
    myFile.write (postReq.responseText)
    myFile.Close

    Set FSO = Nothing
    Set myFile = Nothing

End Sub

Best Answer

This blog post says that ServerXLMHTTP will not work through a proxy on the client, but XMLHTTP will. In Excel VBA I was using ServerXLMHTTP.6.0, and it was failing for some clients inside corporate networks, whereas XMLHTTP worked.

Related Topic