import xml files which are in folder and subfolder into access 2010

[haha3]

hello
i have a question about access 2010 vba
i am novice in programming

so please help me
i have main folder and
there are many sub folder in the main folder

in the subfolder, there are many xml files

my request
programming import all xml files in the first subfolder
and loop the import xml files from first to last subfolder

my main folder : C:\Users\Guest-1\Desktop\test
and sub folder name : C:\Users\Guest-1\Desktop\test\ te1
C:\Users\Guest-1\Desktop\test\ te2
C:\Users\Guest-1\Desktop\test\ te3
.....

Option Compare Database

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 = "C:\Users\Guest-1\Desktop\test\"
    strFile = Dir(strPath & "*.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

[twinnyfo]

There is a method for gathering folder information recursively, so that you are searching all folders under a main folder.

Option Explicit
Option Compare Database

Private Sub cmdClose_Click()
    DoCmd.Close acForm, Me.Form.Name
End Sub
Private Sub cmdMusic_Click()
On Error GoTo EH
    Dim strPath As String
    Dim colFiles As New Collection
    Dim vFile As Variant
    Dim intFiles As Integer
    Dim strFile As String
    Dim strPath As String
    Dim strFileTemp As String
    Dim strPathTemp As String
    Dim intPass As Integer
    Dim fSwap As Boolean
    Dim intNameLen As Integer
    Dim xlApp As Excel.Application
    Dim xlBook As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    Dim intRow As Integer

    strPath = "C:\YourPath\"
    RecursiveDir colFiles, strPath, "*.xml", True
    intFiles = colFiles.Count
    For Each vFile In colFiles
        'Import Your File
    Next vFile
    MsgBox "Your import has completed!", vbOKOnly, "All Done!"
    Exit Sub
EH:
    If Err.Number = 75 Then Resume Next
    MsgBox "There was an error importing!  " & _
        "Please contact your Database Administrator.", vbOKOnly, "WARNING!"
    Exit Sub
End Sub
Private Function RecursiveDir(colFiles As Collection, _
    strFolder As String, _
    strFileSpec As String, _
    bIncludeSubfolders As Boolean)
On Error GoTo EH:
    Dim strTemp As String
    Dim colFolders As New Collection
    Dim vFolderName As Variant

    'Add files in strFolder matching strFileSpec to colFiles
    strFolder = TrailingSlash(strFolder)
    strTemp = Dir(strFolder & strFileSpec)
    Do While strTemp <> vbNullString
        colFiles.Add strFolder & strTemp
        strTemp = Dir
    Loop
    If bIncludeSubfolders Then
        'Fill colFolders with list of subdirectories of strFolder
        strTemp = Dir(strFolder, vbDirectory)
        Do While strTemp <> vbNullString
            If (strTemp <> ".") And (strTemp <> "..") Then
                If (GetAttr(strFolder & strTemp) And vbDirectory) <> 0 Then
                    colFolders.Add strTemp
                End If
            End If
            strTemp = Dir
        Loop
        'Call RecursiveDir for each subfolder in colFolders
        For Each vFolderName In colFolders
            Call RecursiveDir(colFiles, strFolder & vFolderName, strFileSpec, True)
        Next vFolderName
    End If
    Exit Function
EH:
    If Err.Number = 75 Then Resume Next
End Function
Private Function TrailingSlash(strFolder As String) As String
    If Right(strFolder, 1) <> "\" Then
        TrailingSlash = strFolder & "\"
    Else
        TrailingSlash = strFolder
    End If
End Function

I hope this works..... I cut/pasted/slaughtered it from a patch of code I have at home.

[ccming]

@twinnyfo Thanks for you code.
After I ran your code, Access prompt "Invalid use of Me keyword",
What should I change?

[twinnyfo]

Delete lines 4-6 from my code it is unnecessary for this to work.