怎样下载使用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屋!