Download file from url in Excel 2019 (it works on Excel 2007)

Question

I got a code to download a CSV file from a website that requires credentials. I got a code thanks to this website and I could adapted to my needs. My relevant part of code is:

Option Explicit

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

Private Function DownloadUrlFile(URL As String, LocalFilename As String) As Boolean
    Dim RetVal As Long
    RetVal = URLDownloadToFileA(0, URL, LocalFilename, 0, 0)
    If RetVal = 0 Then DownloadUrlFile = True
End Function

Sub DESCARGAR_CSV_DATOS()

Dim EstaURL As String
Dim EsteCSV As String

EstaURL = "https://user:[email protected]/export/targetfile.csv"
EsteCSV = "MyCSV " & Format(Date, "dd-mm-yyyy") & ".csv"

    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
    End With

    DownloadUrlFile EstaURL, _
        ThisWorkbook.Path & "\" & EsteCSV

    DoEvents

    Workbooks.Open ThisWorkbook.Path & "\" & EsteCSV, , True, , , , , , , , , , , True

    'rest is just doing operations and calculations inside workbook

End Sub

Sorry but I cannot provide the real url. Anyways, this code has been working perfectly since September 2019. And it still works perfectly every day.

The computers that execute this code are all of them Windows 7 and Excel 2007 and 64 bits. None of them fail.

But now, this task is going to be outsourced to another office. There, the computers are Excel 2019, Windows 10 and 64 bits.

And the code does not work there. It does not arise any error, but the function DownloadUrlFile does not download any file on Excel 2019 + W10

So to resume, Excel 2007 + Windows 7 works perfectly (tested today). Excel 2019 + Windows 10 does not work (no errors on screen).

Things I've tried to fix it:

1. I've checked that file urlmon.dll exists in system32 and it does
2. I've tried declaring the function URLDownloadToFileA using PtrSafe
3. If I manually type the url in Chrome in the PC with Excel 2019 + W10, the file is downloaded properly, so the URL is ok.

None of this solved my problem. I'm pretty sure the solution it's really easy, because the code works perfectly in Excel 2007, but I can't find what I'm missing here.

I would like to get a code that works in any case, but I would accept also a solution that works only in Excel 2019 and Windows 10 if it's the only way.

Hope somebody can throw some light about this. Thanks in advance.

UPDATE: Tried also the solution in this post but still nothing.

UPDATE 2: Also, tested the code posted here (Excel 2007) with Excel 2010 and it works perfectly.

UPDATE 3: The variable RetVal stores the result of the download. I know some values:

' Returns 0 if success, error code if not.
' Error codes:
' -2146697210 "file not found".
' -2146697211 "domain not found".
' -2147467260 "transfer aborted".

But in my case, it returns -2147221020. What could that be?

UPDATE 4: Well, this is just weird. I've tried same code to download a different file from a public website, and it works in Excel 2019 + W10. I made a new easy code like this:

#If VBA7 And Win64 Then
    Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" _
      Alias "URLDownloadToFileA" ( _
        ByVal pCaller As LongPtr, _
        ByVal szURL As String, _
        ByVal szFileName As String, _
        ByVal dwReserved As LongPtr, _
        ByVal lpfnCB As LongPtr _
      ) As Long
#Else
    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
#End If

Sub Descarga()
Dim EstaURL As String
Dim EsteCSV As String

EstaURL = privateone 'can't be shared, sorry
EsteCSV = "CSV Datos " & Format(Date, "dd-mm-yyyy") & ".csv"

    On Error GoTo Errores
    URLDownloadToFile 0, "https://tutorialesexcel.com/wp-content/uploads/2018/10/funciones.xlsm", ThisWorkbook.Path & "\" & EsteCSV, 0, 0
    URLDownloadToFile 0, EstaURL, ThisWorkbook.Path & "\" & EsteCSV, 0, 0
    Exit Sub
Errores:
    'Si es un bucle lo mejor sería no mostrar ningún mensaje
    MsgBox "Not downloaded", vbCritical, "Errores"
End Sub

The line that says URLDownloadToFile 0, "https://tutorialesexcel.com/wp-content/uploads/2018/10/funciones.xlsm", ThisWorkbook.Path & "\" & EsteCSV, 0, works perfect and downloas the file.

The line URLDownloadToFile 0, EstaURL, ThisWorkbook.Path & "\" & EsteCSV, 0, 0does not work.

So tested again exactly same code but on Excel 2007 and both of them work

Why the first download works and the second one does not on Excel 2019 + W10 but both of them work on Excel 2007+W7?

UPDATE 5: The URL is private, because it contains username and password, but it's like this:

https://user:[email protected]/export/target%20file.csv

Thanks to @Stachu, the URL does not work manually on Internet Explorer on any PC (copy/pasting in the explorer navigation bar, I mean). The URL works perfectly in Google Chrome in all PC.

It's really curious that, manually, the URL on Internet Explorer does not work, but same URL coded with VBA and Executed on Excel2007/2010 works perfectly. Maybe I should change something about the encoding?

UPDATE 6: Still studying all posts by you. The issue here is that I'm just the data guy, the analyst, so plenty of information posted here sounds really hardcore to me.

I've emailed all the info to the IT guys 1 day ago, and still waiting for an answer.

Meanwhile, and based on information here, finally coded something totally different that works on all machines. It works on Windows 7 and 10, and it works on Excel 2007 and 2010 (installed as 32 bits) and Excel 2019 (installed as 64 bits).

I'm adding the code here, so maybe somebody can explain why it works properly, but it looks like the issue was the base64 encoding.

The code I got now is like this (added reference to Microsoft Winhttp Setvices 5.1)

Application.ScreenUpdating = False

Dim whr As WinHttp.WinHttpRequest
Dim oStream As Object
Dim EsteCSV As String
Dim EstaURL As String


EstaURL = "https://user:[email protected]/export/target%20file.csv" 
EsteCSV = "CSV Datos" & Format(Date, "dd-mm-yyyy") & ".csv"

'Set whr = CreateObject("WinHttp.WinHttpRequest.5.1")

Set whr = New WinHttp.WinHttpRequest

whr.Open "GET", EstaURL, True
whr.setRequestHeader "Authorization", "Basic " & EncodeBase64("user" & ":" & "password")
whr.send

' Using 'true' above and the call below allows the script to remain responsive.
whr.waitForResponse
DoEvents

Set oStream = CreateObject("ADODB.Stream")
oStream.Open
oStream.Type = 1
oStream.Write whr.responseBody
oStream.SaveToFile ThisWorkbook.Path & "\" & EsteCSV
oStream.Close
DoEvents

Set oStream = Nothing
whr.abort
Set whr = Nothing
'rest of code for operations

Kill ThisWorkbook.Path & "\" & EsteCSV

Application.ScreenUpdating = True

End Sub

Private Function EncodeBase64(text As String) As String
  Dim arrData() As Byte
  arrData = StrConv(text, vbFromUnicode)

  Dim objXML As Object
  Dim objNode As Object

  Set objXML = CreateObject("MSXML2.DOMDocument")
  Set objNode = objXML.createElement("b64")

  objNode.DataType = "bin.base64"
  objNode.nodeTypedValue = arrData
  EncodeBase64 = objNode.text

  Set objNode = Nothing
  Set objXML = Nothing
End Function

Answer 1

Sub Code is fine. Check the references in tools menu in vba and make declaration ptrsafe as below

Private Declare PtrSafe Function URLDownloadToFileA Lib "urlmon" _

Answer 2

The computers that execute this code are all of them Windows 7 and Excel 2007 and 64 bits. None of them fail.

But now, this task is going to be outsourced to another office. There, the computers are Excel 2019, Windows 10 and 64 bits.

And the code does not work there. It does not arise any error, but the function DownloadUrlFile does not download any file on Excel 2019 + W10

I'm guessing it is not working in another office.

This will only happen if the URL is private and the IPs are not whitelisted. You can check with your networking team for the same whether they have whitelisted the IPs for that URL.

The line that says URLDownloadToFile 0, "https://tutorialesexcel.com/wp-content/uploads/2018/10/funciones.xlsm", ThisWorkbook.Path & "\" & EsteCSV, 0, works perfect and downloas the file.

The line URLDownloadToFile 0, EstaURL, ThisWorkbook.Path & "\" & EsteCSV, 0, 0does not work.

So tested again exactly same code but on Excel 2007 and both of them work

Why the first download works and the second one does not on Excel 2019 + W10 but both of them work on Excel 2007+W7?

Also, It makes no sense that the same code is working perfectly fine for the public URL and not for the private URL except there is an IP restriction.