Re: Importing XML through .NET Web Service with VBA

Since you already have a viable approach (open the XML directly as a
recordset and use copyfrom recordset), why not just test that? It seems to
be the most direct method and there seems to reason to expect it would be
slower than any other....

The other approach would require you to "manually" parse the returned XML
doc and write the values to the *** or contruct an intermediate array and
write that in one shot. Seems like a lot more work.

Tim.


--
Tim Williams
Palo Alto, CA


"brazilnut" <brazilnut.1zn1xm_1133906103.3027@xxxxxxxxxxxxxxxxxxxxx> wrote
in message news:brazilnut.1zn1xm_1133906103.3027@xxxxxxxxxxxxxxxxxxxxxxxx
>
> I need some guidance regarding importing XML data through .NET Web
> Services using VBA.
>
> I currently have a spread*** (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
> spread***. 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 spread*** 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 spread***.
>
> 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.ConvertADODBRecordset2XmlString (var1,
var2, var3))
>
> ' copy xml data to *** 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?action=getinfo&userid=29411
> View this thread: http://www.excelforum.com/showthread.php?threadid=491228
>


.