vba 在excel中调用Web服务
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/474936/
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
Call web service in excel
提问by Jeremy
In a VBA module in excel 2007, is it possible to call a web service? If so, any code snippets? How would I add the web reference?
在 excel 2007 的 VBA 模块中，是否可以调用 Web 服务？如果是这样，任何代码片段？我将如何添加网络参考？
采纳答案by Mostlyharmless
Yes You Can!
是的你可以！
I worked on a project that did that (see comment). Unfortunately no code samples from that one, but googling revealed these:
我参与了一个这样做的项目（见评论）。不幸的是，没有来自那个的代码示例，但谷歌搜索揭示了这些：
How you can integrate data from several Web services using Excel and VBA
如何使用 Excel 和 VBA 集成来自多个 Web 服务的数据
STEP BY STEP: Consuming Web Services through VBA (Excel or Word)
循序渐进：通过 VBA（Excel 或 Word）使用 Web 服务
回答by Mostlyharmless
回答by dgorissen
For an updated answer see this SO question:
有关更新的答案，请参阅此 SO 问题：
calling web service using VBA code in excel 2010
在 excel 2010 中使用 VBA 代码调用 Web 服务
Both threads should be merged though.
两个线程应该合并。
回答by Adnan Shahmir Ahmed
In Microsoft Excel Office 2007 try installing "Web Service Reference Tool" plugin. And use the WSDL and add the web-services. And use following code in module to fetch the necessary data from the web-service.
在 Microsoft Excel Office 2007 中尝试安装“Web 服务参考工具”插件。并使用 WSDL 并添加 Web 服务。并在模块中使用以下代码从网络服务中获取必要的数据。
Sub Demo()
Dim XDoc As MSXML2.DOMDocument
Dim xEmpDetails As MSXML2.IXMLDOMNode
Dim xParent As MSXML2.IXMLDOMNode
Dim xChild As MSXML2.IXMLDOMNode
Dim query As String
Dim Col, Row As Integer
Dim objWS As New clsws_GlobalWeather
Set XDoc = New MSXML2.DOMDocument
XDoc.async = False
XDoc.validateOnParse = False
query = objWS.wsm_GetCitiesByCountry("india")
If Not XDoc.LoadXML(query) Then 'strXML is the string with XML'
Err.Raise XDoc.parseError.ErrorCode, , XDoc.parseError.reason
End If
XDoc.LoadXML (query)
Set xEmpDetails = XDoc.DocumentElement
Set xParent = xEmpDetails.FirstChild
Worksheets("Sheet3").Cells(1, 1).Value = "Country"
Worksheets("Sheet3").Cells(1, 1).Interior.Color = RGB(65, 105, 225)
Worksheets("Sheet3").Cells(1, 2).Value = "City"
Worksheets("Sheet3").Cells(1, 2).Interior.Color = RGB(65, 105, 225)
Row = 2
Col = 1
For Each xParent In xEmpDetails.ChildNodes
For Each xChild In xParent.ChildNodes
Worksheets("Sheet3").Cells(Row, Col).Value = xChild.Text
Col = Col + 1
Next xChild
Row = Row + 1
Col = 1
Next xParent
End Sub
