单击网页中的链接按钮并使用 vba 下载保存 csv 文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14903910/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Click on a link-botton in a web page and download-save csv file using vba
提问by user1889664
I have been trying all different approach to get the table/data from this pageusing Excel VBAbut without any result.
My last attempt is, using Excel VBA, to open the web page click on CSVand save the file in a given location.
我一直在尝试所有不同的方法来使用此页面获取表/数据，Excel VBA但没有任何结果。我的最后一次尝试是使用Excel VBA，打开网页单击CSV并将文件保存在给定位置。
Any help would be very much appreciated.
任何帮助将不胜感激。
回答by David Zemens
Here's another example. This should get you as far as the "Save" dialog box.
这是另一个例子。这应该可以让您到达“保存”对话框。
Sub AnotherExample()
Dim URL As String
Dim ieApp As Object
Dim ieDoc As Object
Dim ieForm As Object
Dim ieObj As Object
Dim objColl As Collection
URL = "http://www.bmreports.com/bsp/BMRSSystemData.php?pT=DDAD&zT=N&dT=NRT"
Set ieApp = CreateObject("InternetExplorer.Application")
ieApp.Visible = True
ieApp.Navigate URL
While ieApp.Busy
'wait...
Wend
Set ieDoc = ieApp.Document
For Each ele In ieApp.Document.getElementsByTagname("span")
If ele.innerHTML = "CSV" Then
DoEvents
ele.Click
'At this point you need to Save the document manually
' or figure out for yourself how to automate this interaction.
End If
Next
ieApp.Quit
End Sub
I do not know how to automate this "Save" interaction, although I am 100% certain it can be done, I'm simply not inclined to spend my time learning how to do it for you.
我不知道如何自动化这个“保存”交互，虽然我 100% 肯定它可以完成，我只是不想花时间学习如何为你做。
回答by David Zemens
I am not able to download any CSV from that link, the site appears to be returning an error. However the XML downloads, so there is data there. I think the problem may be on the website.
我无法从该链接下载任何 CSV，该站点似乎返回错误。但是 XML 会下载，所以那里有数据。我认为问题可能出在网站上。
You can use the QueryTables method of the URL of the CSV file is known (or can be derived). The URL you provide yields "No Data To Display" and an error message "There was an error calling the Web Service"
您可以使用 QueryTables 方法对 CSV 文件的 URL 已知（或可以导出）。您提供的 URL 产生“无数据显示”和错误消息“调用 Web 服务时出错”
Pretty much all of this is from recording a macro using QueryTables, excep a manually input string for fullURLand some rudimentary error-handling.
几乎所有这些都来自使用 QueryTables 记录宏，除了手动输入字符串fullURL和一些基本的错误处理。
Private Sub OpenURL()
'Opens the URL and splits the CSV data in to cells.
Dim fullURL as String '< - variable to contain the URL of the CSV you are attempting to download
'Example URL for CSV download from Yahoo Finance, modify as needed.
fullURL = "http://ichart.finance.yahoo.com/table.csv?s=GM&a=10&b=18&c=2010&d=06&e=27&f=2012&g=d&ignore=.csv"
'This opens the webpage
On Error GoTo ErrOpenURL
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & fullURL, Destination:=Range("A1"))
.Name = fullURL
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingAll
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
ExitOpenURL:
Exit Sub 'if all goes well, you can exit
'Error handling...
ErrOpenURL:
Err.Clear
MsgBox "The URL you are attempting to access cannot be opened.",vbCritical
Resume ExitOpenURL
End Sub
