CODEWITHSUNDEEP
excelvbaurlhttps
vincent b
vincent b

Reputation: 21

Download xls file from URL HTTPS

I am trying to download, using VBA, stock file from URL requesting password.

Sub SaveFileFromURL()

Dim FileNum As Long
Dim FileData() As Byte
Dim WHTTP As Object

mainUrl = "https://wmsukprd.ab-inbev.com/core/Default.html"
fileUrl = "https://wmsukprd.ab- 
inbev.com:30004/SupplyChainAdvantage/WAAdvantageDashboard
/ReportPageExport.ashx/ExportDownload? 
id=1503&WH_ID=24&Item_Number=%25&Location_ID=%25&hu_id=%25&lot_number=
%25&serial_number=%25&_ww_export_id=d231eb2c-64ad-4ce7-82ca-d53c14ae1f69"

filePath = "C:\Users\barbeavi\Desktop\Samlesbury\Stock.csv"

myuser = "my login"
mypass = "******"

strAuthenticate = "start-url=%2F&user=" & myuser & "&password=" & mypass & 
"&switch=Log+In"

Set WHTTP = CreateObject("WinHTTP.WinHTTPrequest.5.1")

WHTTP.Open "POST", mainUrl, False
WHTTP.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
WHTTP.Send strAuthenticate

'GET direct file url
WHTTP.Open "GET", fileUrl, False
WHTTP.Send

FileData = WHTTP.ResponseBody
Set WHTTP = Nothing

'Save the file
FileNum = FreeFile
Open filePath For Binary Access Write As #FileNum
    Put #FileNum, 1, FileData
Close #FileNum

MsgBox "File has been saved!", vbInformation, "Success"

End Sub

I get the message error

Run-time error '-2147012866 (80072efe)':
The connection with the server was terminated abnormally

on the following line:

WHTTP.Send strAuthenticate

The issue may be coming from the HTTPS as I tried the same code on a page which is not secure and here it is working.

Upvotes: 2

Views: 847

Answers (1)

Moe
Moe

Reputation: 1041

In case you have this issue on windows 7 or 8, it most probably is related to VBA sending data with SSL protocol which gets dropped by servers only accepting TLS.

I have been facing the issue of TLS on Windows 7 clients for a while and did one round of fixing based on Microsoft article summarized below:

Step 1. Get Microsoft Update KB3140245: Download relevant (32-bits or 64-bits of user's Windows version) Microsoft Security Protocol Update and install if not already install.

Step 2. Download Microsoft Easy Fix: Download Microsoft “Easy Fix” from Microsoft Support Article, and execute to set TLS 1.1+ as default.

However, later on, I had another round of similar issues which made me realize the fix tool on Microsoft page will not set all the registry keys (missing SChannel Registry Keys). So, I managed to put some scripts together to allow the full fix by setting both Internet Options and SChannel. The SChannel registry keys for TLS1.1/1.2 needs to be added to set enable TLS by default for Winhttp.

WinHttp Keys:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Internet Settings\WinHttp
==> DWORD DefaultSecureProtocols=0x00000A00 (32-bits and 64-bits)

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Windows\CurrentVersion\Internet Settings\WinHttp
==> DWORD DefaultSecureProtocols=0x00000A00 (64-bits)

SCHANNEL Keys:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1\Client 
==> DWORD DisabledByDefault=0x00000000

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Client
==> DWORD DisabledByDefault=0x00000000

Comprehensive Fix: The Microsoft patch does not fix all the registry entries to update WinHTTP defaults and skip over SChannel entries. So, if the two-step fix abpve did not resolve the issue, this github project contains powershell scripts to download and apply all the required registry modification listed above and might be helpful for a more comprehensive one-shot fix: Winttp-TLS

Upvotes: 1

Related Questions