Excel VBA URLDownloadToFile Error for HTTPSresource

Question

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

Yes, the certificate for that URL us only valid for *www.fontshop.at* so the request fails. I am not aware of a method of trivially telling URLDownloadToFile to ignore cert errors, switch to XMLHttp where its simple, E.g. [VBA ServerXMLHTTP https request with self signed certificate](http://stackoverflow.com/questions/11573022/vba-serverxmlhttp-https-request-with-self-signed-certificate) — Alex K., Jan 21 '16 at 13:13
Perhaps this post helps? https://social.msdn.microsoft.com/Forums/ie/en-US/4e39a225-3745-4e2f-bd91-c6fd725d4de4/urldownloadtofile-not-working-in-windows-7-with-https?forum=ieextensiondevelopment — jkpieterse, Jan 21 '16 at 13:17

score 1 · Accepted Answer · edited Dec 30 '19 at 13:00

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 URLDownloadToFile Error for HTTPSresource

1 Answers1

Linked