Import XML Files into Access 2003 via VB

[rrrelic]

Can someone help me with this?

I am running Access 2003 and attempting to import and append multiple XML files into Access. I currently do this process using the Import tool built into Access’s File menu. The only problem with this is that I have to do one file at a time and I have hundreds of files to import.

I would like to be able to use code to loop through all the files contained within a specified folder and import and append the files into my database.
I am not very familiar with writing code but this does not seem like it should be that difficult to do. Here is what I have currently. If someone could help me with this it would be much appreciated.

Private Sub cmdImport_Click()
Dim strFile As Variant
Dim strPath As String
strPath = "Z:\Data\AutoImport"
strFile = Dir(strPath & "savfile*.XML")
Do While Len(strFile) > 0
Application.ImportXML strPath & strFile, 2
Loop
MsgBox "Import Completed"
End Sub

[MMcCarthy]

OK, try this. It will build an array of the list of files and then cycle them for import

Private Sub cmdImport_Click()
Dim strFile As String 'Filename
Dim strFileList() As String 'File Array
Dim intFile As Integer 'File Number
Dim strPath As String ' Path to file folder
 
    strPath = "Z:\Data\AutoImport\"
    strFile = Dir(strPath & "savfile*.XML")
 
    While strFile <> ""
         'add files to the list
        intFile = intFile + 1
        ReDim Preserve strFileList(1 To intFile)
        strFileList(intFile) = strFile
        strFile = Dir()
    Wend
 
     'see if any files were found
    If intFile = 0 Then
        MsgBox "No files found"
        Exit Sub
    End If
 
    'cycle through the list of files
    For intFile = 1 To UBound(strFileList)
        Application.ImportXML strPath & strFileList(intFile), 2
    Next intFile
 
    MsgBox "Import Completed"
 
End Sub