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:
So, my specific questions:
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
A client computer can use the XMLHTTP object ( MSXML2. XMLHTTP. 3.0 ) to send an arbitrary HTTP request, receive the response, and have the Microsoft® XML Document Object Model (DOM) parse that response.
The ServerXMLHTTP object is commonly used to: Receive XML documents from an Active Server Pages (ASP) page on a local or remote Web server (HTTP GET ). Post XML documents to an ASP page on a local or remote Web server (HTTP POST ).
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.
Welcome to VBA and StackOverflow. Your notes are thorough and so the only thing I can suggest is that you check your proxy settings.
https://support.microsoft.com/en-us/help/289481/you-may-need-to-run-the-proxycfg-tool-for-serverxmlhttp-to-work
That link was buried in this link
https://support.microsoft.com/en-us/help/290761/frequently-asked-questions-about-serverxmlhttp
which you were referred to by ComIntern
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With