List files in folder path using keyword?

mattbnorris

New Member
Joined
Sep 17, 2019
Messages
22
Office Version
  1. 2016
Platform
  1. Windows
Hello, I'm currently using the VBA below to create a list of files from the folder paths in C5 and C6 of my excel sheet. Is there a way I can adjust the code so it only returns files in the folder that contain a specific word?

VBA Code:
Sub ListFiles()

Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim objSubFolder As Object
Dim i As Integer

Dim fPathPrior As String
Dim fPathCurr As String

fPathPrior = Range("C5").Value
fPathCurr = Range("C6").Value

Range("B9:T10000").Value = ""
Range("B9:T10000").Interior.Color = vbWhite

On Error GoTo errHandler

'Create an instance of the FileSystemObject

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolderPrior = objFSO.GetFolder(fPathPrior)
Set objFolderCurr = objFSO.GetFolder(fPathCurr)

r = 9

'loops through each file in the directory and prints their names and path

For Each objFile In objFolderPrior.Files

Cells(r, 2) = objFile.Name
Cells(r, 11) = objFile.path

r = r + 1

Next objFile

r = 9

'loops through each file in the directory and prints their names and path

For Each objFile In objFolderCurr.Files

Cells(r, 12) = objFile.Name
Cells(r, 21) = objFile.path

r = r + 1

Next objFile

On Error GoTo 0
Exit Sub

errHandler:

MsgBox "FOLDER PATH FOR NEW SHEETS DOES NOT EXIST!", , "Error"

Exit Sub

End Sub
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,791
Office Version
  1. 365
Platform
  1. Windows
You can do that using Dir like
VBA Code:
Sub ListFiles()

Dim fPathPrior As String
Dim fPathCurr As String
Dim Fname As String

fPathPrior = Range("C5").Value
fPathCurr = Range("C6").Value

Range("B9:T10000").Value = ""
Range("B9:T10000").Interior.Color = vbWhite

On Error GoTo errHandler

Fname = Dir(fPathPrior & "*some text*")
R = 9
Do While Fname <> ""
   Cells(R, 2) = Fname
   Cells(R, 11) = fPathPrior & Fname
   R = R + 1
   Fname = Dir
Loop


On Error GoTo 0
Exit Sub

errHandler:

MsgBox "FOLDER PATH FOR NEW SHEETS DOES NOT EXIST!", , "Error"


End Sub
 
Solution

mattbnorris

New Member
Joined
Sep 17, 2019
Messages
22
Office Version
  1. 2016
Platform
  1. Windows
You can do that using Dir like
VBA Code:
Sub ListFiles()

Dim fPathPrior As String
Dim fPathCurr As String
Dim Fname As String

fPathPrior = Range("C5").Value
fPathCurr = Range("C6").Value

Range("B9:T10000").Value = ""
Range("B9:T10000").Interior.Color = vbWhite

On Error GoTo errHandler

Fname = Dir(fPathPrior & "*some text*")
R = 9
Do While Fname <> ""
   Cells(R, 2) = Fname
   Cells(R, 11) = fPathPrior & Fname
   R = R + 1
   Fname = Dir
Loop


On Error GoTo 0
Exit Sub

errHandler:

MsgBox "FOLDER PATH FOR NEW SHEETS DOES NOT EXIST!", , "Error"


End Sub

Hello Fluff, thanks for your quick response, however I don't think this is quite what i'm looking for.

That Fname variable will be the folder path from C5 in the sheet concatenated with the file name... In my folder path there are multiple files with different names, I want this VBA program to list a certain subset of the files from the folder based on a keyword. From the example below there are many files within the folder path, but I only want the ones that contain the word 'Exhibits' to be found and listed. Can this be done without moving and isolating the files to their own folder?

Example:
List of files in folder pathList of files to be listed by the VBA program
Sales 2407.xlsxExhibits - Cars 05.xlsx
Expenditure 2382.xlsxExhibits - Bus 05.xlsx
Exhibits - Cars 05.xlsxExhibits - Taxis 05.xlsx
Exhibits - Bus 05.xlsxExhibits - Project 05.xlsx
Exhibits - Taxis 05.xlsx
Exhibits - Project 05.xlsx
Calendar 3434.xlsx
Management Control.xlsx
Pricing 4343.xlsx
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,791
Office Version
  1. 365
Platform
  1. Windows
If you use
VBA Code:
Fname = Dir(fPathPrior & "*Exhibits*")
It will do what your code does for the path in C5. You can then duplicate this part
VBA Code:
Fname = Dir(fPathPrior & "*some text*")
R = 9
Do While Fname <> ""
   Cells(R, 2) = Fname
   Cells(R, 11) = fPathPrior & Fname
   R = R + 1
   Fname = Dir
Loop
using the path in C6
 

mattbnorris

New Member
Joined
Sep 17, 2019
Messages
22
Office Version
  1. 2016
Platform
  1. Windows
If you use
VBA Code:
Fname = Dir(fPathPrior & "*Exhibits*")
It will do what your code does for the path in C5. You can then duplicate this part
VBA Code:
Fname = Dir(fPathPrior & "*some text*")
R = 9
Do While Fname <> ""
   Cells(R, 2) = Fname
   Cells(R, 11) = fPathPrior & Fname
   R = R + 1
   Fname = Dir
Loop
using the path in C6
Thanks Fluff, understand that I will need to replicate the code for the second list.

VBA Code:
Sub ListFiles()

Dim fPathPrior As String
Dim fPathCurr As String
Dim Fname As String

fPathPrior = Range("C5").Value
fPathCurr = Range("C6").Value

Range("B9:T10000").Value = ""
Range("B9:T10000").Interior.Color = vbWhite

On Error GoTo errHandler

Fname = Dir(fPathPrior & "*Exhibits*")
R = 9

Do While Fname <> ""
 Cells(R, 2) = Fname
 Cells(R, 11) = fPathPrior & Fname
 R = R + 1
 Fname = Dir
Loop

On Error GoTo 0
Exit Sub

errHandler:
MsgBox "FOLDER PATH FOR NEW SHEETS DOES NOT EXIST!", , "Error"
Exit Sub

End Sub

The code wasn't working as first as it was skipping past the Do While Loop, however I realised that the file path that I had stated in C5 didn't have a \ at the end so it when concatenating the path and name it was causing the Fname variable to blank out.

I have now fixed this and it's working perfectly, thanks so much for the help!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,791
Office Version
  1. 365
Platform
  1. Windows
Glad you sorted it & thanks for the feedback.
 

Forum statistics

Threads
1,147,818
Messages
5,743,378
Members
423,791
Latest member
kevinlee_5

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
Top