multiple xml imports from local path

[Martijn]

I'm struggling with the import of xml files in access.

I receive the files in outlook and save them as client(1).xml, client(2).xml
etc in a local folder.
When I'm using the xml import function in code I only can import a file I
named in the code.

example:
Application.ImportXML DataSource:="D:\Clienten XML\client.xml",
ImportOptions:=acAppendData
(I can put in the same rule with client(2).xml etc, but there are a lot of
files this way).

This works fine but how can I tell the import engine that I want to import
all the *.xml files from "this location" etc.

I think I have to use a loop but I don't know how to define the code.

Can somebody help me?

Thanks

 

[John Nurick]

Hi Martijn,

Another MVP, Joe Fallon, posted this code a while ago to show how to use
Dir() to loop through all the files in a folder. You'll need to replace
DoCmd.TransferText with Application.ImportXML and make a few other
changes.

How to Import all Files in a Folder:

Private Sub btnImportAllFiles_Click()
'procedure to import all files in a directory and delete them.
'assumes they are all the correct format for an ASCII delimited import.
Dim strfile As String

ChDir ("c:\MyFiles")
strfile = Dir("FileName*.*")
Do While Len(strfile) > 0
DoCmd.TransferText acImportDelim, "ImportSpecName", "AccessTableName",
"c:\MyFiles\" & strfile, True
'delete the file (consider moving it to an Archive folder instead.)
Kill "c:\MyFiles\" & strfile
strfile = Dir
Loop

End Sub

 

[Martijn]

Hi John,

Thanks for your replie!
I did copy, paste and changed the code many times to get the result.
Nothing worked, no error was given or files deleted / imported.

The last change I made is written below, so maybe you (or another nice
person can hekp me further out ;-))

I made realy a lot of changes in the application.import code, like
datasource:="strfile" etc, etc.

What code will see the right file?, because it seems that there are no files
to import.
(Ofcourse I checked every way)

The many files I got are named:

client.xml
client1.xml
client2.xml
etc.

the last change I made:
***

Private Sub btnImportAllFiles_Click()

Dim strfile As String

ChDir ("z:\XML_FILES")
strfile = Dir("Client*.xml")
Do While Len(strfile) > 0

Application.ImportXML DataSource:="z:\XML_FILES\" & strfile,
importoptions:=acAppendData

Kill "Z:\XML_FILES\" & strfile
strfile = Dir
Loop

End Sub

***

It is a hard one!

But thanks anyway!

 

[John Nurick]

Hi Martijn,

Using Dir() in a loop like that is a long-established standard way of
processing multiple files in a folder in VBA, VB, and earlier versions of
Basic, and your code looks all right (although I have little experience with
Application.ImportXML).

In the VB editor, hit Ctrl-G to open the Immediate pane and then type:

ChDir "z:\XML_FILES"
? Dir("*.xml")

You should see the name of one of the XML files in the folder. Then type

? Dir()

This should produce the name of the next XML file.

If that is not happening, either you have typed the wrong folder name, or
there are no XML files in the folder - or perhaps the files are hidden.

 

[Martijn]

Hi John,

I did what you suggested: and look

ChDir "z:\XML_FILES"
? Dir("*.xml")

?dir("\XML_FILES\*.xml")
Client.xml
?dir()
Client1.xml

So the chdir isn't working.

I found out that if the standard db map is C:\My documents, the ?Dir("*.*")
gives the first file in c:\My documents.

The solving must be that I call the "\XML_FILES\ dir "on every loop

I changed the code to:
***
Private Sub btnImportAllFiles_Click()

Dim strfile As String

ChDir ("z:\XML_FILES")
strfile = Dir("\XML_FILES\Client*.xml")
Do While Len(strfile) > 0

Application.ImportXML DataSource:="& strfile", importoptions:=acAppendData


'delete the file (consider moving it to an Archive folder instead.)
Kill "Z:\XML_FILES\*.xml"
strfile = Dir
Loop

End Sub
***

Then I got a error -2146697203 (800c000d) on running and Method ImportXML
of object_Application failed
(my translated Dutch to Enlish)

So now I am a little bit further, and have to rewrite and puzzle on the code
in that line.

But still I am very happy that you're trying to help me out!

regards, Martijn

 

[John Nurick]

Martijn,

The reason ChDir() seems to fail is (I am pretty sure) that your current
drive is C: and therefore
ChDir "z:\XML_FILES"
is changing the current directory on Z: but leaving the current drive as
C:. This means that
?Dir("*.xml")
will go to the current directory on the current drive (e.g. "C:\...\My
Documents"). You can check this by using
?CurDir()
which returns what Access thinks is the current directory.

So one way to solve the problem is to explicitly change the drive

ChDir "Z:\XML_FILES"
ChDrive "Z:"
strfile = Dir("*.xml")
Do While Len(strfile) > 0
Application.ImportXML strFile, acAppendData
strFile = Dir()
Loop
...

Alternatively, avoid using ChDir and ChDrive altogether. You need to
remember that when you do
?Dir("z:\XML_FILES\*.xml")
you get
Client.xml
but what you need to pass to Application.ImportXML is the full filespec
z:\XML_FILES\Client.xml
so you need to do something like this:

Dim strFolder As String
...
strFolder = "z:\XML_FILES\"
strFile = Dir(strFolder & "*.xml")
Do While Len(strFile) > 0
Application.ImportXML strFolder & strFile, acAppendData
strFile = Dir()
Loop



When you do
?Dir("z:\XML_FILES\*.xml")
you get
Client.xml
but what you need to pass to Application.ImportXML is the full filespec
z:\XML_FILES\Client.xml