Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing XML through .NET Web Service with VBA
I need some guidance regarding importing XML data through .NET Web Services using VBA. I currently have a spreadsheet (Excel v 2003) that utilizes external data. I currently use ADODB to connect to and retrieve data from my databases. These import procedures are written in VBA code modules and are invoked through various forms and buttons throughout the spreadsheet. These databases are located on a SQL Server (v 5.1 I think). I have local ODBC datasources setup on my PC to access the data. And the queries are written in stored procedures. The problem is that I can only use the spreadsheet on a PC with access to the SQL server. My solution is to setup a .NET Web Service that makes the calls to the DB and returns an XML result set to the calling app (or client application). The difficulty I am having is creating the procedure in VBA to open, read, and copy the XML data from the web service to a range of cells in my spreadsheet. This is what I have so far: Here is the WebMethod I have setup on the web service: Code: -------------------- ‘ convert an adodb recordset into xml and return the a string of xml Public Function ConvertADODBRecordset2XmlString(ByRef rst As ADODB.Recordset) As String Dim oDomDoc As New Interop.MSXML5.DOMDocument40Class rst.Save(oDomDoc, ADODB.PersistFormatEnum.adPersistXML) Return oDomDoc.xml End Function -------------------- Here is the code I have in my VBA code module: Code: -------------------- Public Sub GetData() Dim objSClient As MSSOAPLib30.SoapClient30 Dim oXML As MSXML2.DOMDocument40 ' Point the SOAP API to the web service that we want to call... Set objSClient = New SoapClient30 Call objSClient.mssoapinit(par_WSDLFile:="http://localhost/WebService/WebService.wsdl") 'create new empty XML document Set oXML = New DOMDocument40 ' Call the web service and get requested XML document Call oXML.LoadXml(objSClient.ConvertADODBRecordset2XmlS tring (var1, var2, var3)) ' copy xml data to sheet in Excel ???WHAT CODE DO I USE HERE???? Set oXML = Nothing Set objSClient = Nothing End Sub -------------------- Before I was using the Range(“A1”).CopyFromRecordset rst method. This worked GREAT. Very fast and easy to use. The code I have now seems to work fine. I simply don’t know of a good way to get the XML data in the cells, like I did with ADODB. Does this make sense? Thank you. PS – I have considered converting the xml back into a recordset and then use the .CopyFromRecordset procedure. But I don’t know how slow this would make the app. The speed of the data imports is very fast. I would like to keep it that way. -- brazilnut ------------------------------------------------------------------------ brazilnut's Profile: http://www.excelforum.com/member.php...o&userid=29411 View this thread: http://www.excelforum.com/showthread...hreadid=491228 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing XML through .NET Web Service with VBA
Thank you very much. This is exactly what I needed. -- brazilnut ------------------------------------------------------------------------ brazilnut's Profile: http://www.excelforum.com/member.php...o&userid=29411 View this thread: http://www.excelforum.com/showthread...hreadid=491228 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing XML through .NET Web Service with VBA
I'm having some trouble. My web method returns a string of XML. What i the best way to load that xml string into the recordset? I can't see to figure it out. Thanks -- brazilnu ----------------------------------------------------------------------- brazilnut's Profile: http://www.excelforum.com/member.php...fo&userid=2941 View this thread: http://www.excelforum.com/showthread.php?threadid=49122 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing XML through .NET Web Service with VBA
For those who may find it useful, I have figured it out. Here is th algorithm: Code ------------------- Public Sub GetXML() Dim objSClient As MSSOAPLib30.SoapClient30 ' soap object to access and expose web service interface Dim oXML As MSXML2.DOMDocument40 ' xml document object Dim rst As New ADODB.Recordset ' ADODB recordset Dim str As New ADODB.Stream ' ADODB stream ' create new soap client Set objSClient = New SoapClient30 'create new empty XML document Set oXML = New DOMDocument40 ' Point the SOAP API to the web service that we want to call... ' the wsdl file contains the scheme for the webservice Call objSClient.mssoapinit(par_WSDLFile:="http://localhost/WebService.wsdl") ' Call the web service and load requested XML document in to MSXML DOM document structure Call oXML.LoadXml(objSClient.GetXMLString()) ' open sream str.Open ' save xml document from web service to stream oXML.Save str ' set starting position to beginning of stream str.Position = 0 ' open recordset from stream rst.Open str ' copy recordset to range of cells Range("A1").CopyFromRecordset rst Set oXML = Nothing Set objSClient = Nothing Set rst = Nothing Set stream = Nothing End Su ------------------- My next step is to figure out how to get the field names from the XM file -- brazilnu ----------------------------------------------------------------------- brazilnut's Profile: http://www.excelforum.com/member.php...fo&userid=2941 View this thread: http://www.excelforum.com/showthread.php?threadid=49122 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
007 service pack 1 | Charts and Charting in Excel | |||
Importing Alan Beban's code on Arrays; Importing a module or a project | Excel Worksheet Functions | |||
Can a service account be embedded in Workbook so that by default the macros are run with service account credentials and not the user credentials??? | Excel Discussion (Misc queries) | |||
Difference between a Service Release and a Service Pack? | Excel Programming | |||
Call a Web Service ??? | Excel Programming |