Solved: Import XML Attribute Data To Worksheet

[Marcster]

Hi People,

I'm trying to import XML data that is contained in XML Attributes to a worksheet.

I can load into memory the XML file:

Sub Test()
Dim xmlDoc As New DOMDocument
Dim fileOK As Boolean
Dim myNode As IXMLDOMElement
Dim myCNode As IMXMLDOMNode

fileOK = xmlDoc.Load("Test.xml")

End Sub


XML File Data Sample:

<xml version="1.0" encoding="UTF-8"?>
<data>
<accmvmnt cd="36" tdate="2012-04-24" c="JP" tcode="DEA" ttype="RET" cli="G_4851" ag="" no="30503" amt="3802.71" fno="50" pl="G_ABC" />
<accmvmnt cd="12" tdate="2012-04-24" c="JP" tcode="ABC" ttype="DET" cli="A_4851" ag="" no=30501" amt="-3802.71" fno="40" pl="G_ABC" />
</data>

So in Excel worksheet:
Top row has cd, tdate,c,tcode,ttype,cli,ag,no,amt,fno,pl
Row 2 has the values in "" above.

Any ideas????....

:banghead:

[Kenneth Hobs]

Is that complete xml text? ADO could be used if the file was a standard xml.

MSXML can be used but you would have to parse the data by walking through nodes. In a similar method, the chilkat library can be used. http://www.chilkatsoft.com/downloads_ActiveX.asp

I used msxml and the chilkat methods in a WordPerfect and vb.net example at: http://www.wpuniverse.com/vb/showthread.php?32352-XML-Fun-Function-Examples-using-MSXML2-and-Chilkat&highlight=chilkat

For a standard xml formatted file, something along these lines might work. This fails on the Open line.

' Tools > References... > Microsoft ActiveX Data Objects 2.8 Library
' Tools > References... > Microsoft OLE DB Simple PRovider 1.5 Library
' Tools > References... > Microsoft XML, 6.0
Sub GetMarksterXMLData()
Dim GetXMLDB As ADODB.Connection
Dim sSQL As String, oRS As ADODB.Recordset, oFld As ADODB.Field
Dim lCount As Integer, oSh As Worksheet
Dim xmlFilename As String

xmlFilename = "x:\xml\Marcster.xml"
If Dir(xmlFilename) = "" Then Exit Sub
Set oSh = Worksheets("Sheet1")

Set GetXMLDB = New ADODB.Connection
Set oRS = New ADODB.Recordset
With GetXMLDB
.Open "Provider=MSDAOSP; Data Source=MSXML2.DSOControl;"
.CursorLocation = adUseClient
End With

oRS.Open xmlFilename, GetXMLDB
sSQL = "SELECT * FROM Table"
oRS.Open sSQL, GetXMLDB

For Each oFld In oRS.Fields
lCount = lCount + 1
oSh.Cells(1, lCount).Value = oFld.Name
Next oFld
If Not oRS.EOF Then oSh.Range("A2").CopyFromRecordset oRS
oRS.Close
Set oRS = Nothing
Set GetXMLDB = Nothing
End Sub

[Marcster]

Thanks Kenneth, with your help this is solved.
The .Open fails but works when:
xmlFilename = "\\x:\xml\Marcster.xml"

Thanks again :-).

[Kenneth Hobs]

Good deal. If you don't mind, can you attach the workbook so others can see your final code and the objects that you referenced?

Microsoft says to use another method but that was the one that looked ok to me.

[snb]

Did you consider using:


Workbooks.OpenXML Filename:="G:\OF\BrianExampleXML.XML", LoadOption:= xlXmlLoadImportToList

[Marcster]

Ooops, should of typed:

xmlFilename = "//X:\\XML\Marcster\marcster.xml"
If Dir(Right(xmlFilename, Len(xmlFilename) - 2)) = "" Then Exit Sub


Connecting to .xml file via ADO, cool :-).

I tried Workbooks.OpenXML but opens in a new workbook all the data,
that isn't what I want.

Thanks

[Kenneth Hobs]

For file naming conventions for the ADO Open, see: http://msdn.microsoft.com/en-us/library/ms675260%28VS.85%29.aspx

My msdaosp.dll at work does not register. When I tinkered at home last night, it still had problems with the xml ADO Open.

For snb's method, it should be easy to copy the open workbook's data, close it and then paste to your workbook. Of course that method had problems for me. I suspect that it has to do with the xml file that I created from your text here.

[snb]

what's simpler than using builtin features ?

sub snb()
with Workbooks.OpenXML("G:\OF\BrianExampleXML.XML", xlXmlLoadImportToList )
.sheets(1).cells(1).currentregion.copy thisworkbook.sheets(1).cells(1)
.close 0
end with
end Sub

[snb]

I applied the code to this XML file: see the attachment


PS. Kenneth I wasn't able to reply you PM's. This forum apparently doesn't allow me....

[snb]

I couldn't upload an xml file so I zipped it.

[Kenneth Hobs]

Yes, I got the PM. Quote is the only method that I know to reply to a PM here. I just delete the quoted text.

Using your code and file snb, I got 4 rows with the /CSL on the first cell. Several of the fieldnames in row 2 were prefixed with /Message/@.

Using this method which is much the same, snb's xml imports as I would expect with the first row containing the fieldnames with autofilter set. Rows 2 and 3 have the same data. Weird...

Sub OpenXMLFile()
Dim xmlFilename As String
'xmlFilename = "x:\xml\Marcster.xml"
xmlFilename = "x:\xml\BrianExampleXML.XML"
Workbooks.OpenXML Filename:=xmlFilename, LoadOption:=xlXmlLoadImportToList
ActiveWorkbook.Sheets(1).Cells(1).CurrentRegion.Copy ThisWorkbook.Sheets(1).Cells(1)
Application.CutCopyMode = False
ActiveWorkbook.Close False
End Sub

Opened snb's xml file in Notepad++, it showed the first line as blank and the next with all the data on that line.

[Kenneth Hobs]

This code worked for the books.xml file that I created from the commented link. I could not figure out the syntax for the SQL to just show one or two fields though. This also worked with snb's example xml. It did not work with the original xml file from the first post. I am sure that it is some issue with my xml file.

Using the OpenXMLFile posted earlier with the books.xml worked out nicely.

' http://msdn.microsoft.com/en-us/library/ms675260%28VS.85%29.aspx
' Tools > References... > Microsoft ActiveX Data Objects 2.8 or 6.0 Library
' Not needed per se: Tools > References... > Microsoft XML, 6.0

' Books: http://www.perfectxml.com/articles/xml/importxmlsql.asp
Sub GetMarksterXMLData()
Dim GetXMLDB As ADODB.Connection
Dim sSQL As String, oRS As ADODB.Recordset, oFld As ADODB.Field
Dim lCount As Integer, oSh As Worksheet
Dim xmlFilename As String

'xmlFilename = "c:\myfiles\excel\xml\Marcster.xml"
'xmlFilename = "x:\xml\BrianExampleXML.xml"
xmlFilename = "x:\xml\books.xml"

If Dir(xmlFilename) = "" Then Exit Sub
Set oSh = Worksheets("Sheet1")

Set GetXMLDB = New ADODB.Connection
Set oRS = New ADODB.Recordset
With GetXMLDB
.Open "Provider=MSDAOSP; Data Source=MSXML2.DSOControl;"
.CursorLocation = 2 'adUseClient
End With

oRS.Open xmlFilename, GetXMLDB
sSQL = "SELECT Title FROM Table"
'Set oRS = GetXMLDB.Execute(sSQL)

For Each oFld In oRS.Fields
lCount = lCount + 1
oSh.Cells(1, lCount).Value = oFld.Name
Next oFld
If Not oRS.EOF Then oSh.Range("A2").CopyFromRecordset oRS
oRS.Close
Set oRS = Nothing
Set GetXMLDB = Nothing

ActiveSheet.UsedRange.EntireColumn.AutoFit
End Sub

[snb]

You can create your own XML file, saving an Excel workbook as an XML file; at least in E2010.

PS. Application.CutCopyMode = False is redundant if you do not use copy/paste but use .copy destination