Open all files in a folder and its subfolders

[Peachforyou]

Hello together,
i tryed to add all desired files from a folder an its subfolders into a collection and open them in a loop. This helped me a lot: Open all excel files in Folder and Sub Folder
My code works until the files are in the mainfolderpath. Can i store the fullnames in one collection for example: C:\users\mainfolder\subfolder\testfile.txt or what is the easiest way to fix the issue...?
Please see for yourself...

        Dim Destbook As Workbook
        Dim Sourcebook As Workbook
        Dim oFSO As Object
        Dim oFile As Object
        Dim oFolder As Object
        Dim sFolder As Object
        Dim oSubFolder As Object
        Dim oFolderFile As Object
        Dim xFile As String
        Dim xFiles As New Collection
        Dim xFileCount As Long
        Dim xFolder As String
        Dim i  As Integer
        Dim a As Integer
        
        With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Ordnerauswahl"
        .ButtonName = "Dateiordner auswählen"
        .InitialView = msoFileDialogViewList
        If .Show = -1 Then xFolder = .SelectedItems(1)
        If Right(xFolder, 1) <> "\" Then xFolder = xFolder & "\"
        End With
        
        If xFolder = "\" Then
        MsgBox ("Es wurden keine kompatiblen Dateien gefunden oder ausgewählt!")
        Exit Sub
        End If
        
        Set oFSO = CreateObject("Scripting.FileSystemObject")
        Set oFolder = oFSO.GetFolder(xFolder)
        Set oSubFolder = oFolder.SubFolders
        
        For Each oFile In oFolder.Files
        If oFile.Name Like "*.txt" Then
        xFile = oFile.Name
        If xFile <> "" Then
        xFileCount = xFileCount + 1
        xFiles.Add xFile, xFile
        If xFile = "" Then Resume Next
       
        End If
        End If
        Next
       
        For Each sFolder In oSubFolder
        Set oFolderFile = sFolder.Files
        'Dateien im jeweiligen Unterordner durchsuchen
        For Each oFile In oFolderFile
        If oFile.Name Like "*.txt" Then
        xFile = oFile.Name
        xFileCount = xFileCount + 1
        xFiles.Add xFile, xFile
        If xFile = "" Then Resume Next
        
        End If
        Next
        Next
        
        i = 1
        For i = 1 To xFiles.Count
        Debug.Print xFiles(i)
        Next i
    
        Set Destbook = ThisWorkbook
        If xFiles.Count > 0 Then
        For a = 1 To xFiles.Count
        **Set Sourcebook = Workbooks.Open(xFolder & xFiles.Item(a), local:=True)**
        
        Next
        End If
        
        Set oFSO = Nothing
        Set oFolder = Nothing
        Set oSubFolder = Nothing
        Set oFolderFile = Nothing
        
        End Sub

 

[John_w]

This line only stores the file name in the collection.

xFiles.Add xFile, xFile

Change the loops to:

    For Each oFile In oFolder.Files
        If oFile.Name Like "*.txt" Then
            xFileCount = xFileCount + 1
            xFiles.Add oFile.Path, oFile.Path
        End If
    Next
   
    For Each sFolder In oFolder.SubFolders
        'Dateien im jeweiligen Unterordner durchsuchen
        For Each oFile In sFolder.Files
            If oFile.Name Like "*.txt" Then
                xFileCount = xFileCount + 1
                xFiles.Add oFile.Path, oFile.Path
            End If
        Next
    Next

And change this line:

**Set Sourcebook = Workbooks.Open(xFolder & xFiles.Item(a), local:=True)**

To:

Set Sourcebook = Workbooks.Open(xFiles.Item(a), local:=True)

 

[Peachforyou]

Thank you very much
This solved the problem.