如何使用 VBA 下载文件(不使用 Internet Explorer)

问题描述

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

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.

为此我找到了很多使用由 VBA 控制的 Internet Explorer 的示例.然而，它大多是缓慢的解决方案，而且大多数也很复​​杂.

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.

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

推荐答案

此解决方案基于此网站:http://social.msdn.microsoft.com/Forums/en-US/bd0ee306-7bb5-4ce4-8341-edd9475f84ad/excel-2007-use-vba-to-download-save-csv-from-url

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

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

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

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

这篇关于如何使用 VBA 下载文件(不使用 Internet Explorer)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！