[LillyIsaacson]

How to download web facing files from sharepoint site using VBA

[Abhey]

Basic HTTP Authentication from VBA
http://ramblings.mcpher.com/Home/excelquirks/snippets/basicauth

Also: Excel 2007: use VBA to download & save CSV from URL
https://social.msdn.microsoft.com/Forums/en-US/bd0ee306-7bb5-4ce4-8341-edd9475f84ad/excel-2007-use-vba-to-download-save-csv-from-url

The following requires Internet Explorer but will download a file in VBA:

Dim myURL As String
myURL = "http://www.somesite.com/file.csv"

Dim WinHttpReq As Object
Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
WinHttpReq.Open "GET", myURL, False
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 ("C:\file.csv")
    oStream.Close
End If