I have the following code below to limit the selection. I need the code below to exclude files containing "ListORefinancing" in the file name for e.g. ListOfRefinancing_Items 8869815.xlsx
it would be appreciated if someone could assist me in amending my code
it would be appreciated if someone could assist me in amending my code
Code:
Sub SelectFilesand_ConvertToCSV()
Dim strFolderPath As String
Dim selectedFiles() As String
Dim validFiles() As String
Dim selectedFile As Variant
Dim objFD As FileDialog
Dim ws As Worksheet
Dim cell As Range
Dim excludeKeyword As String
Dim fileFilter As String
' Specify the folder path
strFolderPath = "C:\Pull\"
excludeKeyword = "ListOfRefinancing"
' Get the latest modified files matching the specified criteria
selectedFiles = GetLatestFiles(strFolderPath, "ePMz_ListOfgoods_Inquiry", ".xlsx")
If UBound(selectedFiles) = -1 Then
MsgBox "No matching files found.", vbExclamation
Exit Sub
End If
' Reference the worksheet with file names
Set ws = ThisWorkbook.Sheets("Convert")
' Build the list of valid file names from column E
ReDim validFiles(0 To ws.Cells(ws.Rows.Count, "E").End(xlUp).Row - 1)
For Each cell In ws.Range("E1:E" & ws.Cells(ws.Rows.Count, "E").End(xlUp).Row)
validFiles(cell.Row - 1) = cell.value
Next cell
' Create and configure the file dialog
Set objFD = Application.FileDialog(msoFileDialogFilePicker)
With objFD
.AllowMultiSelect = True
.Title = "Select Files"
.InitialFileName = selectedFiles(0) ' Use the first file as the initial file name
.Filters.Clear
.Filters.Add "Excel Files", "*.xls;*.xlsx"
' Filter the selected files based on the valid file names
.FilterIndex = 2 ' Excel Files filter
.InitialFileName = strFolderPath ' Set the initial folder
' Show the file dialog
If .Show = 0 Then Exit Sub
' Process the selected files
For Each selectedFile In .SelectedItems
' Check if the selected file is in the list of valid files
If IsValidFile(selectedFile, validFiles) Then
' Your processing code here for selected files
Debug.Print selectedFile ' Example: Print the selected file path
End If
Next selectedFile
End With
Set objFD = Nothing
End Sub
Function IsValidFile(ByVal filePath As String, ByRef files() As String) As Boolean
Dim file As Variant
For Each file In files
If filePath = file Then
IsValidFile = True
Exit Function
End If
Next file
IsValidFile = False
End Function
Function GetLatestFiles(folderPath As String, fileNamePrefix As String, fileExtension As String) As String()
Dim latestFiles() As String
Dim latestDate As Date
Dim file As String
Dim fileCount As Long
latestDate = DateSerial(1900, 1, 1)
' Count the number of matching files
file = Dir(folderPath & fileNamePrefix & "*" & fileExtension)
Do While file <> ""
If file Like fileNamePrefix & "*" & fileExtension Then
If FileDateTime(folderPath & file) > latestDate Then
latestDate = FileDateTime(folderPath & file)
fileCount = 1
ElseIf FileDateTime(folderPath & file) = latestDate Then
fileCount = fileCount + 1
End If
End If
file = Dir
Loop
' Populate the array with matching files
On Error Resume Next
ReDim latestFiles(0 To fileCount - 1)
file = Dir(folderPath & fileNamePrefix & "*" & fileExtension)
Do While file <> ""
If file Like fileNamePrefix & "*" & fileExtension And FileDateTime(folderPath & file) = latestDate Then
latestFiles(fileCount - 1) = folderPath & file
fileCount = fileCount - 1
End If
file = Dir
Loop
GetLatestFiles = latestFiles
End Function
