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

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

As for your error, -2147221020 => 0x800401E4 as per VBA Error Codes and Descriptions this error is MK_E_SYNTAX which is 'invalid moniker syntax'.

When it says moniker I guess it means your url and to be honest the web address does not look syntactically correct...

"https://user:[email protected]/export/targetfile.csv"

I'd have to dig around to see if that truly met the web standard for a url. In the meantime I'd suggest figuring out a different url. It maybe that an upgrade to urlmon.dll now complains about the url whereas the Windows 7 version didn't.

Ok, my bad, actually it looks like you can do such uris, in theory, so I have a uri fragment

first-client:noonewilleverguess@localhost:8080/oauth/token taken from OAuth2 Boot

Ok, so it is valid, re top of page 17 rfc3986.

authority = [ userinfo "@" ] host [ ":" port ]

Looks like you'll have to drop into Windows API calls to set username and password. So here is sample code

Option Explicit

'* with thanks to http://www.holmessoft.co.uk/homepage/WininetVB.htm

Private Declare Function InternetOpen Lib "wininet.dll" Alias "InternetOpenA" ( _
    ByVal lpszAgent As String, _
    ByVal dwAccessType As Long, _
    ByVal lpszProxyName As String, _
    ByVal lpszProxyBypass As String, _
    ByVal dwFlags As Long) As Long


Private Enum InternetOpenAccessTypes
    INTERNET_OPEN_TYPE_PRECONFIG = 0 'Retrieves the proxy or direct configuration from the registry.
    INTERNET_OPEN_TYPE_DIRECT = 1 'Resolves all host names locally.
    INTERNET_OPEN_TYPE_PROXY = 3 'Passes requests to the proxy unless a proxy bypass list is supplied and the name to be resolved bypasses the proxy. In this case, the function uses INTERNET_OPEN_TYPE_DIRECT.
    INTERNET_OPEN_TYPE_PRECONFIG_WITH_NO_AUTOPROXY = 4 'Retrieves the proxy or direct configuration from the registry and prevents the use of a startup Microsoft JScript or Internet Setup (INS) file.
End Enum


Private Declare Function InternetConnect Lib "wininet.dll" Alias "InternetConnectA" ( _
    ByVal hInternetSession As Long, _
    ByVal lpszServerName As String, _
    ByVal nServerPort As Integer, _
    ByVal lpszUsername As String, _
    ByVal lpszPassword As String, _
    ByVal dwService As Long, _
    ByVal dwFlags As Long, _
    ByVal dwContext As Long) As Long

Private Const INTERNET_SERVICE_HTTP = 3

Private Const INTERNET_FLAG_NO_COOKIES = &H80000
Private Const INTERNET_FLAG_NO_CACHE_WRITE = &H4000000

Private Declare Function HttpOpenRequest Lib "wininet.dll" Alias "HttpOpenRequestA" ( _
    ByVal hHttpSession As Long, _
    ByVal lpszVerb As String, _
    ByVal lpszObjectName As String, _
    ByVal lpszVersion As String, _
    ByVal lpszReferer As String, _
    ByVal lpszAcceptTypes As String, _
    ByVal dwFlags As Long, _
    ByVal dwContext As Long) As Long

Private Declare Function HttpSendRequest Lib "wininet.dll" Alias "HttpSendRequestA" ( _
    ByVal hHttpRequest As Long, _
    ByVal lpszHeaders As String, _
    ByVal dwHeadersLength As Long, _
    ByVal lpOptional As String, _
    ByVal dwOptionalLength As Long) As Boolean

Private Declare Function InternetReadFile Lib "wininet.dll" ( _
    ByVal hFile As Long, _
    ByVal lpBuffer As String, _
    ByVal dwNumberOfBytesToRead As Long, _
    ByRef lpNumberOfBytesRead As Long) As Boolean

Private Sub Test()
    Dim hInternet As Long
    hInternet = InternetOpen("Mozilla", INTERNET_OPEN_TYPE_DIRECT, vbNullString, vbNullString, 0)
    If hInternet = 0 Then
        Debug.Print "InternetOpen failed"
        GoTo SingleExit
    End If

    Dim sUSERNAME As String
    sUSERNAME = "foo"

    Dim sPASSWORD As String
    sPASSWORD = "bar"


    Dim hConnect As Long
    hConnect = InternetConnect(hInternet, "www.microsoft.com", 80, sUSERNAME, sPASSWORD, INTERNET_SERVICE_HTTP, 0, 0)
    If hConnect = 0 Then
        Debug.Print "InternetConnect failed"
        GoTo SingleExit
    End If

    Dim lFlags As Long
    Dim hRequest As Long

    lFlags = INTERNET_FLAG_NO_COOKIES
    lFlags = lFlags Or INTERNET_FLAG_NO_CACHE_WRITE

    hRequest = HttpOpenRequest(hConnect, "GET", "www.microsoft.com", "HTTP/1.0", vbNullString, vbNullString, lFlags, 0)

    Dim bRes As Boolean
    bRes = HttpSendRequest(hRequest, vbNullString, 0, vbNullString, 0)

    Dim strFile As String
    strFile = "downloadedfile.txt"

    Dim strBuffer As String * 1
    Dim strDir As String
    strDir = Dir(ThisWorkbook.Path & "\" & strFile)
    If Len(strDir) > 0 Then
        Kill ThisWorkbook.Path & "\" & strFile
    End If

    Dim iFile As Long
    iFile = FreeFile()
    Open ThisWorkbook.Path & "\" & strFile For Binary Access Write As iFile

    Do
        Dim lBytesRead As Long
        bRes = InternetReadFile(hRequest, strBuffer, Len(strBuffer), lBytesRead)
        If lBytesRead > 0 Then
            Put iFile, , strBuffer
        End If
    Loop While lBytesRead > 0

    Debug.Print "finished"
SingleExit:


End Sub

UPDATE: Congratulations on your solution for which you invite an explanation, perhaps see this MSDN Forum where the discourse outlines the different technology stacks. If I browse the C++ header file urlmon.h then URLDownloadToFile looks like its based on WinInet. So switching to WinHTTP is a smart move to a more server based stack.
Also, on the same stack logic, I believe you could have used MSXML2.ServerXMLHTTP see this VBScript newsgroup archive

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.