Excel VBA HTTPSresource的URLDownloadToFile错误 [英] Excel VBA URLDownloadToFile Error for HTTPSresource
问题描述
我尝试使用VBA从Excel中的服务器下载文件.使用HTTP时,此方法工作正常,但使用HTTPS时,则无效.
I try to download a file from a Server in Excel using VBA. This works fine when using HTTP, but doesn't work using HTTPS.
我可以在Internet Explorer中同时访问两种地址(HTTP/HTTPS).如果我将URLDownloadToFile
与HTTP地址一起使用,则会下载文件.
I can reach both adresses (HTTP/HTTPS) in Internet Explorer. If I use URLDownloadToFile
with the HTTP address the file is downloaded.
使用HTTPSadress
时,我得到返回码-2146697211
.也许这是证书问题?
When using the HTTPSadress
I get return code -2146697211
. Maybe this a certificate Problem?
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
推荐答案
如果其他人遇到此问题:对我而言,问题是服务器期望客户端证书.通常,https调用在VB中是没有问题的.对于自签名证书,必须从文件系统或Windows证书存储发送证书.
If anybody else has this Problem: The Problem for me was, that the Server expected a Client Certificate. Normally https calls are no Problem from VB. For self signed certs, one has to send a certificate from file System or Windows cert store.
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
这篇关于Excel VBA HTTPSresource的URLDownloadToFile错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!