Excel VBA Msxml2.XMLHTTP.6.0与Msxml2.ServerXMLHTTP.6.0 [英] Excel VBA Msxml2.XMLHTTP.6.0 vs Msxml2.ServerXMLHTTP.6.0

问题描述

我是一名自学成才的业余程序员，并且是该论坛的新手.请忍受我.

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

大约两年前，我编写了一个简单的Excel vba程序，用于登录网站并以.csv文件的形式获取客户对帐单.我的程序利用GET和POST请求.直到大约三周前，该程序才能正常运行(满足我的需要)，当时不幸的是，该程序对我不利.该程序无法通过初始GET请求.具体来说，它将在getReq.send行上中断.

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.

我遇到了这篇文章: 使用MSXML2.XMLHTTP而不是InternetExplorer.Application登录到网站使用VBA

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

在这里，我了解到可以使用"Msxml2.XMLHTTP.6.0"代替"Msxml2.ServerXMLHTTP.6.0".我相应地修改了我的代码，无需在Get请求之后解析cookie，并且它起作用了！但是我不知道.即使我可以使用它，但在整个过程中我仍然没有学到很多东西.

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.

一些需要注意的信息:

• 我的原始程序在我的工作计算机(WindowsXP)上损坏了.
• 发现这可能是XP的问题，无论如何在新机器的市场上，我都更新到了运行Windows7的新计算机.该程序仍然无法正常运行，尽管我收到了另一条错误消息.
• 我在Windows10计算机上运行了代码，效果很好.
• 我使用相同的代码连接到其他各种网站，无论使用什么操作系统，它都可以正常工作.

所以，我的具体问题:

1. 为什么代码可以与Msxml2.XMLHTTP.6.0一起使用但不能与Msxml2.ServerXMLHTTP.6.0一起使用?
2. 为什么代码首先会被破坏?
3. 为什么代码只能在一个特定的网站上工作，而不能在另一个网站上工作?

任何见识将不胜感激.我已经附上了我的代码(登录信息已X出).

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

推荐答案

此博客文章说ServerXLMHTTP不能通过客户端上的代理工作，但是XMLHTTP可以.在Excel VBA中，我使用的是ServerXLMHTTP.6.0，但对于公司网络内的某些客户端来说却失败了，而XMLHTTP起作用了.

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.

这篇关于Excel VBA Msxml2.XMLHTTP.6.0与Msxml2.ServerXMLHTTP.6.0的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！