Open multiple Excel files based on partial file name

Question

I would like to select multiple files (as shown); eg. "DataSource Quality", "DataSource Security", "DataSource Shipping", "DataSource Warehouse".

To add on, if the file name selected is "DataSource Quality 2020", etc, it should still be a valid selection. Meaning to say, so long the filename contains the names as shown in the screenshot above, it should still be considered correct.

However, if any one of the files selected are wrong (wrong partial file name), there should be a message box that says "No/Wrong file selected".

Dim hasRun As Boolean

Sub RunOnlyOnce()

    Application.DisplayAlerts = True

    If hasRun = False Then
        
        Dim fNameAndPath As Variant
        fNameAndPath = Application.GetOpenFilename(FileFilter:="All Files (*.*), *.*", Title:="Select Files To Be Opened", MultiSelect:=True)
        Debug.Print fNameAndPath
        Debug.Print Dir(fNameAndPath)

        If Dir(fNameAndPath) = "DataSource.xlsx" Then
            Workbooks.Open Filename:=fNameAndPath
            hasRun = True
            Exit Sub
        Else
            MsgBox "No/Wrong file selected. ", vbExclamation, "Oops!"
            ThisWorkbook.Saved = False
            Application.Quit

        End If
    End If
    
End Sub

Answer 1

You can define a Function to get a list of all the files in a folder. There is already a answer in SO for that at the following link: https://stackoverflow.com/a/31428399/6908282

Below is the same code.

Public Function listfiles(ByVal sPath As String)
    
    Dim vaArray     As Variant
    Dim i           As Integer
    Dim oFile       As Object
    Dim oFSO        As Object
    Dim oFolder     As Object
    Dim oFiles      As Object

    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set oFolder = oFSO.GetFolder(sPath)
    Set oFiles = oFolder.Files

    If oFiles.Count = 0 Then Exit Function

    ReDim vaArray(1 To oFiles.Count)
    i = 1
    For Each oFile In oFiles
        vaArray(i) = oFile.Name
        i = i + 1

    Next

    listfiles = vaArray
    
  End Function

After this is defined you can use this function in a procedure to loop through all the files if they match you criteria using the LIKE Operator and open the files that match the criteria. See example below

Public Sub abc()
Dim Path As String, file As Variant, i As Integer

Path = "D:\Parent Folder\Sub Folder"
i = 1
For Each file In listfiles(Path)
 If file Like "test*" And file Like "*.xlsm" Then MsgBox file  ' replace "MsgBox file" with "Workbooks.Open Path & "\" & file"
    i = i + 1
 Next


End Sub

Note: In the above code make sure to edit 4 things

1. "Path" variable
2. "test*" condition. Replace test with DataSource if all you files start with DataSource. DO NOT remove the "*" as this is a wild card match to match anything after that.
3. "*.xlsm". This is to check the file type of the file. Change xlsm to xlsx or xlsm or remove the condition entirely if you want all excel files.
4. Currently, the procedure only shows a Msgbox for all the matching files, you need to replace "MsgBox file" with "Workbooks.Open Path & "\" & file"

Check the LIKE Operator documentation to know more about comparing string patterns.

Answer 2

Here's my code:

Sub SubOpenDataSourceFiles()

    'Declarations.
    Dim WrkMotherWorkbook As Workbook
    Dim VarFiles As Variant
    Dim IntCounter01 As Integer
    Dim StrFileName As String
    Dim StrMarker As String

    'Setting variables.
    StrMarker = "DataSource"
    Set WrkMotherWorkbook = ActiveWorkbook

    'Request the user what files to open.
    VarFiles = Application.GetOpenFilename(FileFilter:="All Files (*.*), *.*", _
                                           Title:="Select Files To Be Opened", _
                                           MultiSelect:=True _
                                          )

    'Checking if it has been selected any file.
    On Error GoTo No_File_Selected
    IntCounter01 = UBound(VarFiles)
    On Error GoTo 0

    'Scrolling through the files.
    For IntCounter01 = 1 To UBound(VarFiles)

        'Setting the variable in order to analyse the file name.
        StrFileName = Split(VarFiles(IntCounter01), "\")(UBound(Split(VarFiles(IntCounter01), "\")))

        'Checking if the left part of the file name differs from StrMarker.
        If Left(StrFileName, Len(StrMarker)) <> StrMarker Then
            'If it does differ, a message box pops up.
            MsgBox "Unauthorized file.", vbExclamation, StrFileName
        Else
            'If it doesn't differ, it opens the file (assuming it's not a corrupted file).
            Workbooks.Open Filename:=CStr(VarFiles(IntCounter01))
        End If

    Next

    'Activating WrkMotherWorkbook.
    WrkMotherWorkbook.Activate

No_File_Selected:

End Sub

It might not be as elegant as Gangula's one, but still it should work. Only point: i've maintained your "all files" preference when opening the files. Still i'd suggest to filter it to .xlsm or .xls or whatever kind of excel files you are supposed to open. Just like Gangula's did.

Answer 3

I have modified you code to give an example for discerning between the different filename requirements when selecting multiple files:

Sub OpenOnlyValidFiles()
    fNameAndPath = Application.GetOpenFilename(FileFilter:="All Files (*.*), *.*", Title:="Select Files To Be Opened", MultiSelect:=True)

    AllFilesAreValid = True
    For Each FullPathAndName In fNameAndPath  'Test all files to see if they meet requirements
        ' Test only filename and not the path
        fName = Split(FullPathAndName, Application.PathSeparator)(UBound(Split(FullPathAndName, Application.PathSeparator)))
        If Not (fName Like "DataSource Quality*") And _
           Not (fName Like "DataSource Security*") And _
           Not (fName Like "DataSource Shipping*") And _
           Not (fName Like "DataSource Warehouse*") And _
           Not (fName Like "GoodFile*") Then
            AllFilesAreValid = False
        End If
     Next

    If AllFilesAreValid Then 'If all files meet the requirements then open them in Notepad
        For Each FullPathAndName In fNameAndPath
            Shell "NOTEPAD.EXE " & FullPathAndName
        Next
        MsgBox (UBound(fNameAndPath) & " valid files found and opened in notepad")
    Else
        MsgBox ("At least one file was not valid. No files opened.")
    End If
End Sub

Create a folder to test with files in it like:

DataSource Excluded Stuff.txt
DataSource Quality.txt
DataSource Security.txt
DataSource Security 2020.txt
DataSource Shipping.txt
DataSource Warehouse.txt
DataSource Warehouse 2019.txt
DataSource Warehouse 2020.txt
GoodFile.txt
BadFile.txt
GoodFile 2020.txt
BadFile 2020.txt

You can modify the code to check for '.xlsx' or ".xlsm" extensions or to let it open any valid files found instead of rejecting if even one file is invalid.