I am a proud, amateur programmer, and I am new to this forum. Please carry me.
About two years ago, I wrote a simple Excel vba program to enter a site and capture a client application as a CSV file. My program uses GET and POST requests. This program worked perfectly (for my needs) until about three weeks ago, when it, unfortunately, broke me. The program could not pass the initial GET request. In particular, it will be split into getReq.send string.
I came across this post: Log in using MSXML2.XMLHTTP instead of InternetExplorer.Application with VBA
Here I found out that you can use "Msxml2.XMLHTTP.6.0" instead of "Msxml2.ServerXMLHTTP.6.0". I changed my code accordingly, eliminating the need to parse cookies after a Get request, and it worked! But I have no idea. Despite the fact that I got him to work, I don’t think that I learned a lot in this process.
Some information:
- My original program crashed on my working computer (WindowsXP).
- Assuming that this could be an XP problem, and in any case, on the market for a new machine, I will upgrade to a new computer running Windows7. The program still did not work, although I received another error message.
- I ran my code on a computer running Windows10 and it worked fine.
- I use identical code to connect to other sites, and it works great, no matter what operating system.
So my specific questions are:
- Why can the code work with Msxml2.XMLHTTP.6.0, but not with Msxml2.ServerXMLHTTP.6.0?
- And why can the code break in the first place?
- Why does the code work on one particular website, but no other?
Any insight would be very helpful. I attached my code (with X'd login information).
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