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

在这里，我了解到您可以使用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:UsersAdamDesktopPOSITION" & curDate & ".csv", True)
    myFile.write (postReq.responseText)
    myFile.Close

    Set FSO = Nothing
    Set myFile = Nothing

End Sub

推荐答案

这个 blog post 说 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屋！