XML data imports into Excel but not into Access

[TrekBiker]

A client generates data in an XML file and imports it successfully into Excel. The data that's needed is several columns in and several rows down.

It would be much easier to import directly into Access but the Wizard just creates a set of empty tables.

I tried creating an empty table in Import.accdb using just the needed column headers appearing in the Excel import, then ran this

Private Sub Command0_Click()
Dim objAccess As Object
[indent]Const acAppendData = 2
Set objAccess = CreateObject("Access.Application")
objAccess.OpenCurrentDatabase "C:\temp\Import.accdb"
objAccess.ImportXML "c:\temp\drt.xml", acAppendData[/indent]
End Sub

Still no data. Have to say I'm not at all familiar with using XML.

 

[sxschech]

I encountered the same problem with some xml files. Ended up using Access vba to remotely open the file with office automation in excel reformat/manipulate it into a usable layout and then use transferspreadsheet code to get it into access.