List filenames not containing string

Formula11

Active Member
Joined
Mar 1, 2005
Messages
431
Office Version
  1. 365
Platform
  1. Windows
I want to list filenames in a folder but exclude those with the text "Superseded".
Would it work using Instr?

Code:
Sub File_Attributes()
    Dim sFolder As FileDialog
    Set sFolder = Application.FileDialog(msoFileDialogFolderPicker)
    If sFolder.Show = -1 Then
        File_Attributes_List_Files sFolder.SelectedItems(1), True
    End If
End Sub

Sub File_Attributes_List_Files(ByVal SourceFolderName As String, ByVal IncludeSubfolders As Boolean)
    Dim FSO As Object
    Dim SourceFolder As Object
    Dim FileItem As Object
    Dim strFile As String
    Dim FileName As Variant
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set SourceFolder = FSO.GetFolder(SourceFolderName)
    row = ActiveCell.row
    With CreateObject("Scripting.Dictionary")
        For Each FileItem In SourceFolder.Files
            strFile = FileItem.Name
            .Item(strFile) = Array(FileItem.Name)
        Next FileItem
        'Do While InStr(FileName, "") <> Superseded
            For Each FileName In .Items
            Cells(row, 3).Formula = FileName
            row = row + 1
            Next FileName
        'Loop
    End With
End Sub
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Try this.
Code:
With CreateObject("Scripting.Dictionary")
    For Each FileItem In SourceFolder.Files
        strFile = FileItem.Name        
        If InStr(File, "Superseded") = 0 Then 
                Cells(row, 3).Value = strFile
                row = row + 1
        End If
    Next FileItem       
End With
 
Upvote 0
Does this work?

Code:
With CreateObject("Scripting.Dictionary")
    For Each FileItem In SourceFolder.Files
        strFile = FileItem.Name
        .Item(strFile) = Array(FileItem.Name)
    Next FileItem
    For Each Filename In .Items
        If InStr(1, Filename, "Superseded", vbTextCompare) = 0 Then
            Cells(Row, 3).Formula = Filename
            Row = Row + 1
        End If
    Next Filename
End With
 
Upvote 0
In #4, for the InStrt function, I changed the first string parameter to "Filename" or strFile for the macro to run.
But it still lists all the files.
Could it have something to do with declaration: Dim FileName As Variant?
 
Last edited:
Upvote 0
Not sure I looked at this long enough, but I don't see why you need a dictionary. Does this work (untested)?
Code:
Sub File_Attributes_List_Files(ByVal SourceFolderName As String, ByVal IncludeSubfolders As Boolean)
    Dim FSO As Object
    Dim SourceFolder As Object
    Dim FileItem
    Dim strFile As String
    Dim FileName As Variant
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set SourceFolder = FSO.GetFolder(SourceFolderName)
    Row = ActiveCell.Row
       For Each FileItem In SourceFolder.Files
             If InStr(FileItem.Name, "Superseded") = 0 Then
                    Cells(Row, 3).Formula = FileItem.Name
                    Row = Row + 1
              End If
           Next FileItem
End Sub
 
Last edited:
Upvote 0
There was a typo in the code I posted which I couldn't fix because of the board maintenance.

Anyway, here's how it should have looked.
Code:
With CreateObject("Scripting.Dictionary")
    For Each FileItem In SourceFolder.Files
        strFile = FileItem.Name        
        If InStr(strFile, "Superseded") = 0 Then 
                Cells(row, 3).Value = strFile
                row = row + 1
        End If
    Next FileItem       
End With
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,559
Latest member
MrPJ_Harper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top