xml to excel Help!
-
I have an xml datastream and would like to know the syntax to map the contents to a specific excel worksheet and xml map. This is to automate 30+ complex reports in excel (must be excel - and no iis available to make my job much easier!). I anyone has a better solution I welcome it. This is my syntax so far: Dim objConn As ADODB.Connection Dim objCMD As ADODB.Command Dim objResultStream As ADODB.Stream Dim xmpCustomMap As XmlMap On Error GoTo Handle_Error Set xmpCustomMap = ActiveWorkbook.XmlMaps("triangle_Map") Set objConn = New ADODB.Connection objConn.Open "Provider=SQLOLEDB;server=mydb;Database=Development;" & _ "Integrated Security=SSPI" Set objCMD = New ADODB.Command Set objCMD.ActiveConnection = objConn 't-SQL DIALECT objCMD.Dialect = "{C8B522D7-5CF3-11CE-ADE5-00AA0044773D}" objCMD.CommandText = "Exec dbo.proc_xml 1394" objCMD.CommandType = adCmdText Set objResultStream = New ADODB.Stream objResultStream.Open objCMD.Properties("Output stream") = objResultStream objCMD.Properties("XML Root") = "Root" objCMD.Execute , , adExecuteStream Debug.Print objResultStream.ReadText(adReadAll)