(Excel VBA):正在访问JSON文件-操作超时 [英] (Excel VBA): Accessing JSON file - operation timed out

问题描述

我正在尝试从网络上的JSON文件中提取数据.我暂时正在使用一个虚拟JSON文件，以使事情正常进行.我的代码在下面，但是每次都超时，并且不返回任何内容.如果我也使用不同的URL，也会发生同样的情况.

I'm attempting to pull data from a JSON file on the web. I'm using a dummy JSON file for the time being to get things working. My code is below, but it times out every time and doesn't return anything. The same happens if I use different URLs also.

Sub Test()
    Dim strResult As String
    Dim objHTTP As Object
    Dim URL As String
    Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
    URL = "https://jsonplaceholder.typicode.com/posts/2"
    objHTTP.Open "GET", URL, False
    objHTTP.Send
    strResult = objHTTP.ResponseText
    MsgBox strResult
End Sub

在相关的情况下，我在文件中启用了以下库:

In case it's relevant, I have the following libraries enabled in the file:

• Visual Basic for Applications

• Visual Basic for Applications

Microsoft Excel 15.0对象库

Microsoft Excel 15.0 Object Library

OLE自动化

Microsoft脚本运行时

Microsoft Scripting Runtime

Microsoft WinHTTP Services版本5.1

Microsoft WinHTTP Services, version 5.1

我想念什么?

已修复.我不知道WinHttpRequest和XMLHTTPRequest之间的区别.使用后者时，代码可以正常工作.谢谢大家.

Fixed. I wasn't aware of the distinction between WinHttpRequest and XMLHTTPRequest. When using the latter, the code worked fine. Thanks all.

推荐答案

使用 XMLHTTPRequest ?

在使用 WinHttpRequest 时， HTTP 请求的操作系统默认值-例如，代理设置-不使用，必须明确设置:

While using WinHttpRequest the operating system defaults for HTTP requests - proxy settings for example - are not used and must be set explicitly:

Sub Test()
    Dim strResult As String
    Dim objHTTP As Object
    Dim URL As String
    Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
    objHTTP.SetProxy 2, "proxyIP:proxyPort"
    URL = "https://jsonplaceholder.typicode.com/posts/2"
    objHTTP.Open "GET", URL, False
    objHTTP.setCredentials "username", "password", 1
    objHTTP.Send
    strResult = objHTTP.ResponseText
    MsgBox strResult
End Sub

2 "rel="nofollow noreferrer"> IWinHttpRequest :: SetProxy方法是 HTTPREQUEST_PROXYSETTING_PROXY .1 "rel =" nofollownoreferrer> IWinHttpRequest :: SetCredentials方法是 HTTPREQUEST_SETCREDENTIALS_FOR_PROXY .

The 2 in IWinHttpRequest::SetProxy method is HTTPREQUEST_PROXYSETTING_PROXY. The 1 in IWinHttpRequest::SetCredentials method is HTTPREQUEST_SETCREDENTIALS_FOR_PROXY.

使用 XMLHTTPRequest 时， HTTP 请求的操作系统默认值将按照控制面板中 Internet选项中的设置使用.因此，如果您能够通过浏览器访问URL，则应运行以下命令:

While using XMLHTTPRequest the operating system defaults for HTTP requests are used as set in Internet Options in control panel. So the following should run if you are able accessing the URL via browser:

Sub Test()
    Dim strResult As String
    Dim objHTTP As Object
    Dim URL As String
    Set objHTTP = CreateObject("MSXML2.XMLHTTP.6.0")
    URL = "https://jsonplaceholder.typicode.com/posts/2"
    objHTTP.Open "GET", URL, False
    objHTTP.Send
    strResult = objHTTP.ResponseText
    MsgBox strResult
End Sub

这篇关于(Excel VBA):正在访问JSON文件-操作超时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！