怎样下载使用VBA文件（没有Internet Explorer） [英] How do i download a file using VBA (Without 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.

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

推荐答案

本方案基于此网站：
<一href=\"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-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

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

这篇关于怎样下载使用VBA文件（没有Internet Explorer）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！