vba 使用（excel）VBA获取HTTP？

getHTTP with (excel) VBA?

提问by seansilver

I'm searching for a query for an excel VBA macro to get a webpage html code as string. I've found some source with getHTTPrequest but I can't register .net framwork system.dll or link it.

我正在搜索 excel VBA 宏的查询，以获取网页 html 代码作为字符串。我找到了一些带有 getHTTPrequest 的源，但我无法注册 .net 框架 system.dll 或链接它。

any greenhorn friendly snippet out there? ;-) Thanks!

有没有新手友好的片段？;-） 谢谢！

采纳答案by Tomalak

Close enough: How can I send an HTTP POST request to a server from Excel using VBA?— It's even for Excel ;-)

足够接近：如何使用 VBA 从 Excel 向服务器发送 HTTP POST 请求？— 它甚至适用于 Excel ;-)

Just use a GET request instead:

只需使用 GET 请求：

objHTTP.Open "GET", URL, False

MSDN: Using the WinHttpRequest COM Object - Retrieving Data Using Visual Basic

MSDN：使用 WinHttpRequest COM 对象 - 使用 Visual Basic 检索数据

回答by ashleedawg

Here's a compact function that returns the response text of almost any specified URL, such as HTTP source code for a web page, or response text from JSON API's.

这是一个紧凑的函数，它返回几乎任何指定 URL 的响应文本，例如网页的 HTTP 源代码，或来自JSON API 的响应文本。

---

---

Late Bound:

后期绑定：

_{(No references required)}

_{（无需参考）}

Public Function getHTTP(ByVal url As String) As String
  With CreateObject("MSXML2.XMLHTTP")
    .Open "GET", url, False: .Send
    getHTTP = StrConv(.responseBody, vbUnicode)
  End With
End Function

---

---

Early Bound:

早期绑定：

If you need to call the function repeatedly (in a loop, for example) you might be better off to use it as an early-bound function:

如果您需要重复调​​用该函数（例如在循环中），最好将其用作早期绑定函数：

1. Add a referenceto the XML library:

   Tools→ References→ Select Microsoft XML, v6.0(or the highest version you have listed)

2. Declare a module-level variable: (at the top of a module)

   Dim msXML As XMLHTTP60

3. Within the function, only create the XML objectif the variable is not already set:

   Public Function getHTTP(ByVal url As String) As String
     If msXML Is Nothing Then Set msXML = New XMLHTTP60
     With msXML
       .Open "GET", url, False: .Send
       getHTTP = StrConv(.responseBody, vbUnicode)
     End With
   End Function
   

1. 添加对 XML 库的引用：

   Tools→ References→ 选择Microsoft XML, v6.0（或您列出的最高版本）

2. 声明一个模块级变量：（在模块顶部）

   Dim msXML As XMLHTTP60

3. 在函数内，如果变量尚未设置，则仅创建 XML 对象：

   Public Function getHTTP(ByVal url As String) As String
     If msXML Is Nothing Then Set msXML = New XMLHTTP60
     With msXML
       .Open "GET", url, False: .Send
       getHTTP = StrConv(.responseBody, vbUnicode)
     End With
   End Function
   

---

---

Example Usage:_{^{(for either method)}}

示例用法：（_{^{对于任一方法）}}

To get the HTML source code for this page:

要获取此页面的 HTML 源代码：

Debug.Print getHTTP("https://stackoverflow.com/q/817602")

---

---

? NOTE!

Many applications (including MS Office) will now return an error if you attempt to connect to an insecure url with methods like this, or others (ie., Excel's WEBSERVICE).

To avoid this issue, use HTTPS://instead of HTTP://wherever possible.

? 笔记！

如果您尝试使用此类方法或其他方法（即 Excel 的）连接到不安全的 url，许多应用程序（包括MS Office）现在将返回错误WEBSERVICE。

为避免此问题，请尽可能使用HTTPS://代替HTTP://。