使用 Excel VBA，打开网页并保存
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20415293/
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
Using Excel VBA, open a web page and then save it
提问by Air_Cooled_Nut
I'm trying to write a macro that will open a given URL and perform a File >> SaveAs... The URL returns an XML output on the screen and I want to save it. Then I can have Excel open the file and work on it (this part I can do).
我正在尝试编写一个宏来打开给定的 URL 并执行文件 >> 另存为... URL 在屏幕上返回一个 XML 输出，我想保存它。然后我可以让 Excel 打开文件并处理它（这部分我可以做）。
Here's basically what I want to do and it works except for the SaveAs bit:
这基本上是我想要做的，除了 SaveAs 位之外它都可以工作：
Sub Main()
REM Reference to "Microsoft Internet Controls" is being used.
Dim objIE As InternetExplorer
Set objIE = New InternetExplorer
With objIE
.Visible = True
.navigate "http://exampleURL.comany.com/ServerOutput/XmlFile.xml"
Do Until .readyState = 4
DoEvents
Loop
REM The next line fails
.SaveAs "C:\temp\test.xml"
.Application.Quit
End With
Set objIE = Nothing
End Sub
A plain "Save" is fine if that's all that will work. Just looking for a way to save the output on the screen. Thanks!
如果仅此而已，那么简单的“保存”就可以了。只是在寻找一种将输出保存在屏幕上的方法。谢谢！
回答by Jerome Montino
Maybe something like this can help?
也许这样的事情会有所帮助？
Public Sub GetXML()
Dim oXML As New MSXML2.XMLHTTP60
Dim xURL As String
'Retrieve XML.
xURL = "http://exampleURL.comany.com/ServerOutput/XmlFile.xml"
oXML.Open "GET", xURL, False
oXML.send
'Put into sheet.
ThisWorkbook.Sheets("Sheet1").Range("A1").Value = oXML.responseXML.XML
End Sub
EDIT: Make sure the reference to Microsoft XML, v3.0is enabled for this.
编辑：确保Microsoft XML, v3.0为此启用了引用。
