如何使用VBA下载文件（没有Internet Explorer）

I need to download a CSV file from a website using VBA in Excel. The server also needed to authenticate me since it was data from a survey service.

我需要在Excel中使用VBA从网站下载CSV文件。服务器还需要对我进行身份验证，因为它是来自调查服务的数据。

I found a lot of examples using Internet Explorer controlled with VBA for this. However, it was mostly slow solutions and most were also convoluted.

我找到了很多使用VBA控制的Internet Explorer的例子。然而，它主要是缓慢的解决方案，大多数也是错综复杂的。

Update: After a while I found a nifty solution using Microsoft.XMLHTTP object in Excel. I thought to share the solution below for future reference.

更新：过了一段时间，我在Excel中使用Microsoft.XMLHTTP对象找到了一个漂亮的解决方案。我想分享下面的解决方案以供将来参考。

2 个解决方案

#1

This solution is based from this website: http://social.msdn.microsoft.com/Forums/en-US/bd0ee306-7bb5-4ce4-8341-edd9475f84ad/excel-2007-use-vba-to-download-save-csv-from-url

此解决方案基于以下网站：http：//social.msdn.microsoft.com/Forums/en-US/bd0ee306-7bb5-4ce4-8341-edd9475f84ad/excel-2007-use-vba-to-download-save- CSV从 - 网址

It is slightly modified to overwrite existing file and to pass along login credentials.

稍作修改以覆盖现有文件并传递登录凭据。

Sub DownloadFile()

Dim myURL As String
myURL = "https://YourWebSite.com/?your_query_parameters"

Dim WinHttpReq As Object
Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
WinHttpReq.Open "GET", myURL, False, "username", "password"
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", 2 ' 1 = no overwrite, 2 = overwrite
    oStream.Close
End If

End Sub

#2

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

Sub Example()
    DownloadFile$ = "someFile.ext" 'here the name with extension
    URL$ = "http://some.web.address/" & DownloadFile 'Here is the web address
    LocalFilename$ = "C:\Some\Path" & DownloadFile !OR! CurrentProject.Path & "\" & DownloadFile 'here the drive and download directory
    MsgBox "Download Status : " & URLDownloadToFile(0, URL, LocalFilename, 0, 0) = 0
End Sub

Source

资源

I found the above when looking for downloading from FTP with username and address in URL. Users supply information and then make the calls.

我在寻找使用URL中的用户名和地址从FTP下载时找到了上述内容。用户提供信息然后拨打电话。

This was helpful because our organization has Kaspersky AV which blocks active FTP.exe, but not web connections. We were unable to develop in house with ftp.exe and this was our solution. Hope this helps other looking for info!

这很有用，因为我们的组织有卡巴斯基AV阻止活动FTP.exe，但不阻止网络连接。我们无法使用ftp.exe在内部开发，这是我们的解决方案。希望这有助于其他寻找信息！

#1