https Ressource的Excel VBA URLDownloadToFile错误 [英] Excel VBA URLDownloadToFile Error for https Ressource

问题描述

我尝试使用VBA从Excel中的服务器下载文件。使用http，但使用https不起作用。

我可以在Internet Explorer中访问两个地址（http / https）。如果我使用URLDownloadToFile与文件下载的http地址。

使用https地址时，我会得到返回码-2146697211。也许这是证书问题？

干杯，

Frederic

代码：

私人声明函数URLDownloadToFile Liburlmon_
别名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）

如果Ret = 0然后
'MsgBox文件成功下载
Else
MsgBox返回码：&Ret&无法在这里下载Code`enter代码。
End If
End Sub

解决方案

：我的问题是，服务器期望有一个客户端证书。通常https调用不是VB的问题。对于自签名证书，必须从文件系统或Windows证书商店发送证书。

  Dim oStream As Object 
 Dim myURL As String 
 
 myURL =URL
 
 Dim WinHttpReq As Object 
设置WinHttpReq = CreateObject（WinHttp.WinHttpRequest.5.1）
 WinHttpReq.Option（4）= 13056'忽略SSL错误
 
 WinHttpReq.OpenGET，myURL，False 
 
'从Windows证书商店获取证书
'WinHttpReq.SetClientCertificateCURRENT_USER\Root\CERTI
 
 WinHttpReq.setRequestHeaderAccept，* / *
 WinHttpReq.setRequestHeaderUser-Agent，Mozilla / 4.0（兼容; MSIE 6.0; Windows NT 5.0）
 WinHttpReq.setRequestHeader Proxy-Connection，Keep-Alive
 WinHttpReq.Send 
 
 myURL = WinHttpReq.ResponseBody 
如果WinHttpReq.Status = 200然后
设置oStream = CreateObject（ ADODB.Stream）
 oStream.Open 
 oStream.Type = 1 
 oStream.Write WinHttpReq.ResponseBody 
 oStream.SaveToFile Environ（TEMP）& \File，2 
 oStream.Close 
 Else 
 MsgBoxReturncode：& WinHttpReq.Status& 无法下载代码。 
结束如果
  

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.

I can reach both adresses (http/https) in Internet Explorer. If I use URLDownloadToFile with the http address the file is downloaded.

When using the https adress I get returncode -2146697211. Maybe this a certificate Problem?

Cheers,

Frederic

Code:

    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

解决方案

If anyboy 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

这篇关于https Ressource的Excel VBA URLDownloadToFile错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！