help!! Importxml problem

[achan275]

Hi all,
I ran into problem with Importxml in Ac2003. I am writing a piece of code to import xml files to an Access database.
Here is my code:

Private Sub Command0_Click()
Dim myfile, mypath
mypath = "C:\Documents and Settings\X\My Documents\submission\TODAY\*.xml"
myfile = Dir(mypath)

Do Until myfile = ""
    On Error GoTo errhandling1
    Application.ImportXML DataSource:=myfile, ImportOptions:=acAppendData
    myfile = Dir
Loop
MsgBox "Done!!", vbOKOnly
Exit Sub

errhandling1: MsgBox Error(Err)
End Sub

I had been trying another piece of code which cycle through files in a folder using .FileSearch, but it just gave me error and didn't run properly. I decided to try another approach and feed the file names to the Application.Importxml line using DIR. It looks like the DIR part is doing what it's supposed to do, but the Importxml just give me an error saying the method is failed. I tried something similar on another machine, it didn't give error but it just doesn't do anything and skipped every files.
Anyone has any idea?
Please help, thanks!!

AC

 

[PHV]

And what about this changes ?
...
mypath = "C:\Documents and Settings\X\My Documents\submission\TODAY\"
myfile = Dir(mypath & "*.xml")
...
Application.ImportXML DataSource:=mypath & myfile, ImportOptions:=acAppendData
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[achan275]

Yes, I tried using DIR(mypath+myfile) as well as DIR("C:\Documents and Settings\X\My Documents\submission\TODAY\*.xml")

and both gave the same result.

I almost checked everything, I looked at the reference and everything looks alright.

AC