ImportXML, Access 2002, automating import

[StellaIndigo]

Hi everyone

OK, I'm new to programming with Access Basic and/or Visual Basic.

I'm trying to import data from xml files that are present in a folder. The whole process should scan the folder, load each xml file and delete each file afterwards.

I'm getting stuck with my ImportXML method.

I've got

Application.ImportXML( DataSource:="d:\ordered.xml", _
ImportOptions:=acAppendData )

which is valid according to the code completion but the compiler says I'm missing an = after acAppendData )

Can anyone help? or does anyone have any macros/modules for importing multiple XML files into a table.

thanks in advance

Stella

There are 10 types of people in the world. Those that understand binary and those that don't.

 

[Page410]

Are you working in Access? If so, try the code as follows:

ImportXML DataSource:="d:\ordered.xml", ImportOptions:=acAppendData

 

[VBslammer]

You need to lose the parentheses - they're not required unless you use [tt]Call[/tt] before the statement.

VBSlammer

Unemployed in Houston, Texas

 

[StellaIndigo]

Thanks for the help with that. I realised I had to remove the parentheses by looking at some code in the forum. I'm now stuck trying to trap an error with ImportXML

Error number -1072896764
Method 'ImportXML' of object '_Application' failed.

The import file exists and is readable, compliant XML. My procedure for this code is,

Function LoadXMLFiles()
Set fs = Application.FileSearch

On Error GoTo ErrorHandler

With fs
.LookIn = "c:\temp\not"
.filename = "*ac.xml"
If .Execute(SortBy:=msoSortbyFileName, _
SortOrder:=msoSortOrderAscending) > 0 Then
For i = 1 To .FoundFiles.Count
Application.ImportXML DataSource:=.FoundFiles(i), ImportOptions:=acAppendData
Kill .FoundFiles(i)
Next i
End If
End With

Exit Function

ErrorHandler:
MsgBox Err.Number & " " & Err.Description, vbOKOnly

End Function


Any ideas?

There are 10 types of people in the world. Those that understand binary and those that don't.

 

[VBslammer]

Are you using Access 2002?

I've only been able to use the ImportXML with tables I exported first from Access using Application.ExportXML

The help file for this topic is horrible, and shows named arguments that don't even exist on my system.

Here's a link that has some additional info

http://www.databasejournal.com/features/msaccess/article.php/1465481

*** Your code looks good to me.



VBSlammer

Unemployed in Houston, Texas

 

[PatrickWiegers]

StellaIndigo Have you been able to solve the issue since I ran into the same issue.....

Patrick Wiegers

 

[vbajock]

Access's built in XML stuff sucks. Rather than trying to make that klooge work, invest the time in learning the MSXML Document Object Model. You can download the SDK here:

http://www.microsoft.com/downloads/...28-7071-4979-8a67-3cffcb0c2524&displaylang=en

The best book to go with the DOM is WROX press's "VB6 XML". Although the book is written for the Vb6 compiler, the same code and concepts will work in VBA.