通过 Excel VBA 从 WCF 服务调用方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11154896/
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 a method from a WCF Service through Excel VBA
提问by CallumVass
I have created a method through a WCF webservice. I've uploaded it to my server. What I'd like to do is call that method in an Excel VBA macro. Is it possible?
我通过 WCF 网络服务创建了一个方法。我已经上传到我的服务器了。我想做的是在 Excel VBA 宏中调用该方法。是否可以？
Something like:
就像是：
Dim client As DaybookServicesClient = New DaybookServicesClient()
' Use the 'client' variable to call operations on the service.
' Calls my method
client.ExecuteSQLJob()
' Always close the client.
client.Close()
How would I reference my service in Excel VBA?
我将如何在 Excel VBA 中引用我的服务？
采纳答案by Brijesh Mishra
one approach could be create WebGet wcf service,service would return adorecordset xml, on macro you can fetch recordset using following code
一种方法是创建 WebGet wcf 服务，服务将返回 adorecordset xml，在宏上，您可以使用以下代码获取记录集
Public Function GetRSFromString(sXML As String) As Object
Dim oStream As Object, oRecordset As Object
Set oStream = CreateObject("ADODB.Stream")
oStream.Open
oStream.WriteText sXML
oStream.Position = 0
Set oRecordset = CreateObject("ADODB.Recordset")
oRecordset.Open oStream
oStream.Close
Set oStream = Nothing
Set GetRSFromString = oRecordset
Set oRecordset = Nothing
End Function
Public Function GetSoapRequest()
Dim strResult As String
Dim xmlhtp As Object, xmlDoc As Object, oRecordSetFromXML As Object
Set xmlhtp = CreateObject("msxml2.xmlhttp.6.0")
With xmlhtp
.Open "get", "http://[server]/ServiceName.svc/FunctionName", False
.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
.Send
Set xmlDoc = CreateObject("msxml2.DOMDocument.6.0")
strResult = .responseText
xmlDoc.loadXML strResult
Set oRecordSetFromXML = AdoFunction .GetRSFromString(xmlDoc.Text)
End With
Set xmlDoc = Nothing
Set xmlhtp = Nothing
End Function
回答by tom redfern
You can call web services from excel using the Web Service Reference Toolto generate a proxy for you service and then making a call in VBA code.
您可以使用Web 服务参考工具从 excel 调用 Web 服务，为您的服务生成代理，然后在 VBA 代码中进行调用。
However, I have tried to get this working before and failed due to interoperability between VB and Java (which was hosting the service).
但是，由于 VB 和 Java（托管该服务）之间的互操作性，我之前曾尝试过使其工作，但失败了。
If your calls are one way (the VBA code doesn't require a response) it would be better to have the VBA code send a message via msmq to an intermediary service which will then make the call on your behalf. This is the solution I ended up using.
如果您的调用是一种方式（VBA 代码不需要响应），最好让 VBA 代码通过 msmq 向中介服务发送消息，然后中介服务代表您进行调用。这是我最终使用的解决方案。
