HTTPSresource 的 Excel VBA URLDownloadToFile 错误

Question

我尝试使用 VBA 从 Excel 中的服务器下载文件。这在使用 HTTP 时工作正常，但在使用 HTTPS 时不起作用。

我可以在 Internet Explorer 中访问这两个地址 (HTTP/HTTPS)。如果我使用 URLDownloadToFile使用 HTTP 地址下载文件。

使用 HTTPSadress 时我得到返回码 -2146697211 .也许这是一个证书问题？

Private Declare Function URLDownloadToFile Lib "urlmon" _
Alias "URLDownloadToFileA" (ByVal pCaller As Long, _
ByVal szURL As String, ByVal szFileName As String, _
ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long

Dim Ret As Long

Sub DownloadCode()

Dim strURL As String
Dim strPath As String
strURL = "https:/url.de/module.bas"
strPath = Environ("TEMP") & "\Module.bas"
Ret = URLDownloadToFile(0, strURL, strPath, 0, 0)

If Ret = 0 Then
'   MsgBox "File successfully downloaded"
Else
    MsgBox "Returncode:" & Ret & " Unable to download Code`enter code here`."
End If

End Sub

Answer 1

如果其他人有这个问题:对我来说问题是，服务器需要一个客户端证书。通常，来自 VB 的 https 调用没有问题。对于自签名证书，必须从文件系统或 Windows 证书存储区发送证书。

Dim oStream As Object
Dim myURL As String

myURL = "URL"

Dim WinHttpReq As Object
Set WinHttpReq = CreateObject("WinHttp.WinHttpRequest.5.1")
WinHttpReq.Option(4) = 13056 ' Ignore SSL Errors

WinHttpReq.Open "GET", myURL, False

'Grab Cert from Windows Cert Store
'WinHttpReq.SetClientCertificate "CURRENT_USER\Root\CERTI"

WinHttpReq.setRequestHeader "Accept", "*/*"
WinHttpReq.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
WinHttpReq.setRequestHeader "Proxy-Connection", "Keep-Alive"
WinHttpReq.Send

myURL = WinHttpReq.ResponseBody

If WinHttpReq.Status = 200 Then
    Set oStream = CreateObject("ADODB.Stream")
    oStream.Open
    oStream.Type = 1
    oStream.Write WinHttpReq.ResponseBody
    oStream.SaveToFile Environ("TEMP") & "\File", 2
    oStream.Close
Else
    MsgBox "Returncode:" & WinHttpReq.Status & " Unable to download  Code."
End If