search jpg file name in folders and subfolders and copy result file names

S

Sunil7598

New Member
Joined
Dec 28, 2022
Messages
20
Office Version
  1. 2016
Platform
  1. Windows
Hi Guys,

Am trying to get all the jpg file names related to 1 product. e.g. this is the product name 37004378_TM search this in file explorer & copy all the result file names like 37004378_TM-99999999_01.jpg 37004378_TM.jpg 37004378_TM_B.jpg 37004378_TM_B3.jpg 37004378_TM_D1.jpg 37004378_TM_D2.jpg 37004378_TM_D3.jpg 37004378_TM_D4.jpg 37004378_TM_D5.jpg 37004378_TM_D8.jpg

Tried a lot of things and already exceeded the deadline. Have to finish ASAP.

Thank you for helping me
 
Sort by date Sort by votes
Here a recursive macro. Only change your file path in the first macro.

VBA Code: 
Dim ar() As Variant, xFile As Variant, x As Long

Public Sub file_search()
 For Each xFile In Range("B2", Range("B" & Rows.Count).End(xlUp))
   getFile "C:\Users\xxx\Documents\"
 Next
 Cells(1, 5).Resize(x) = Application.Transpose(ar)
 End
End Sub

Public Sub getFile(objFolderPath As String)
 Dim sFold, it, sf
 With CreateObject("scripting.filesystemobject")
    Set sFold = .GetFolder(objFolderPath)
    For Each it In sFold.Files
       If it.Name Like xFile & "*" Then
          ReDim Preserve ar(x)
          ar(x) = it.Path
          x = x + 1
       End If
    Next
    For Each sf In sFold.SubFolders
       getFile sf.Path
    Next
 End With
End Sub
 
Upvote 0

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
With a few clicks:
Power Query: 
let
    Source = Folder.Files("C:\Users\jdell\Dropbox\Photos"),
    LowercasedText = Table.TransformColumns(Source,{{"Extension", Text.Lower, type text}}),
    FilteredRowsJPG = Table.SelectRows(LowercasedText, each ([Extension] = ".jpg")),
    RemovedColumns = Table.RemoveColumns(FilteredRowsJPG,{"Content"}),
    ExpandedAttributeSizw = Table.ExpandRecordColumn(RemovedColumns, "Attributes", {"Size"}, {"Size"}),
    ChangedType = Table.TransformColumnTypes(ExpandedAttributeSizw,{{"Size", Int64.Type}})
in
    ChangedType
to get this
Book1
ABCDEFG
1NameExtensionDate accessedDate modifiedDate createdSizeFolder Path
23TimeLoser.jpg.jpg12/27/2022 11:4811/15/2020 18:4411/15/2020 18:44195659C:\Users\xxxxx\Dropbox\Photos\
3AtlantisRollerCoaster600dpi.jpg.jpg12/27/2022 11:4801/09/2020 14:2111/21/2020 16:483369140C:\Users\xxxxx\Dropbox\Photos\
4Blue-nami.jpg.jpg12/27/2022 11:4810/27/2020 18:4810/27/2020 18:48192168C:\Users\xxxxx\Dropbox\Photos\
5Buds.jpg.jpg12/27/2022 11:4802/04/2022 13:1802/04/2022 13:1871207C:\Users\xxxxx\Dropbox\Photos\
6Dad's Obituary.jpg.jpg12/27/2022 11:4802/20/2022 09:4602/20/2022 09:4632227C:\Users\xxxxx\Dropbox\Photos\
7FSTL.jpg.jpg12/27/2022 11:4811/09/2020 16:4811/09/2020 16:4847368C:\Users\xxxxx\Dropbox\Photos\
8FSTL50.jpg.jpg12/27/2022 11:4811/15/2020 15:3511/15/2020 15:3535738C:\Users\xxxxx\Dropbox\Photos\
9FSTL75.jpg.jpg12/27/2022 11:4811/15/2020 15:3511/15/2020 15:3515675C:\Users\xxxxx\Dropbox\Photos\
10Hong Kong Hot Sauce.jpg.jpg12/27/2022 11:4809/14/2020 16:0909/14/2020 16:09352528C:\Users\xxxxx\Dropbox\Photos\
11IMG_20150903_145857.jpg.jpg12/15/2022 10:1809/03/2015 16:0406/30/2020 19:122483059C:\Users\xxxxx\Dropbox\Photos\
12Kyle&Jewel-October 2019.jpg.jpg12/15/2022 10:1812/26/2019 13:0106/30/2020 19:12587572C:\Users\xxxxx\Dropbox\Photos\
13ME-200x231.jpg.jpg11/03/2022 08:1006/13/2010 19:2006/30/2020 19:1210423C:\Users\xxxxx\Dropbox\Photos\
14ME.jpg.jpg11/03/2022 08:1010/08/2010 12:3206/30/2020 19:1231413C:\Users\xxxxx\Dropbox\Photos\
15Mom's Wedding and Honeymoon Announcement.jpg.jpg12/15/2022 10:1802/20/2022 09:3102/20/2022 09:31120201C:\Users\xxxxx\Dropbox\Photos\
16Oliver's Birthday Summer 1999 - 2.jpg.jpg12/15/2022 10:1805/26/2000 13:2106/30/2020 19:1295500C:\Users\xxxxx\Dropbox\Photos\
17Scary.jpg.jpg12/15/2022 10:1806/02/2020 14:2906/30/2020 19:12265974C:\Users\xxxxx\Dropbox\Photos\
18Summer2005.jpg.jpg12/25/2022 17:5309/04/2011 07:0806/30/2020 19:1223369C:\Users\xxxxx\Dropbox\Photos\
Sheet6

Best of all, if files are added, the table can be updated with a quick right click refresh.
None of the code was generated by hand, just the PQ UI.
Just say'n....
 
Upvote 0
This is for all files. If you want to match the list of names in column B, you need more steps. That is not that easy though.
 
Upvote 0
JEC said:
This is for all files. If you want to match the list of names in column B, you need more steps. That is not that easy though.
Click to expand...
You're kidding, right?
  1. Load table of names desired into PQ.
  2. Merge names table with the folder to a new query
  3. Load table.
Book1
I
1Names
2IMG_1803.jpg
3IMG_1810.jpg
4IMG_2024.jpg
5IMG_2069.jpg
6IMG_2074.jpg
7IMG_2077.jpg
8IMG_2078.jpg
9IMG_2079.jpg
10IMG_2215.jpg
11IMG_2247.jpg
12IMG_2250.jpg
Sheet6

Merged to this
Book1
KLMNOPQR
1NamesNameExtensionDate accessedDate modifiedDate createdSizeFolder Path
2IMG_1803.jpgIMG_1803.jpg.jpg12/21/2022 04:3811/20/2020 18:0311/20/2020 18:032929347C:\Users\xxxxx\Dropbox\Photos\Cassise Family\
3IMG_1810.jpgIMG_1810.jpg.jpg11/03/2022 08:1011/20/2020 18:0311/20/2020 18:033096721C:\Users\xxxxx\Dropbox\Photos\Cassise Family\
4IMG_2024.jpgIMG_2024.jpg.jpg11/03/2022 08:1011/26/2020 19:3711/26/2020 19:37868582C:\Users\xxxxx\Dropbox\Photos\Cassise Family\
5IMG_2069.jpgIMG_2069.jpg.jpg12/06/2022 06:1812/01/2020 17:2812/01/2020 17:281665068C:\Users\xxxxx\Dropbox\Photos\Cassise Family\
6IMG_2074.jpgIMG_2074.jpg.jpg11/03/2022 08:1012/01/2020 17:2712/01/2020 17:271528637C:\Users\xxxxx\Dropbox\Photos\Cassise Family\
7IMG_2077.jpgIMG_2077.jpg.jpg12/26/2022 00:0612/01/2020 17:2712/01/2020 17:271387866C:\Users\xxxxx\Dropbox\Photos\Cassise Family\
8IMG_2078.jpgIMG_2078.jpg.jpg12/27/2022 21:5012/01/2020 17:2812/01/2020 17:281459815C:\Users\xxxxx\Dropbox\Photos\Cassise Family\
9IMG_2079.jpgIMG_2079.jpg.jpg11/03/2022 08:1012/01/2020 17:2712/01/2020 17:271660883C:\Users\xxxxx\Dropbox\Photos\Cassise Family\
10IMG_2215.jpgIMG_2215.jpg.jpg12/16/2022 09:2412/25/2020 14:4012/25/2020 09:402247995C:\Users\xxxxx\Dropbox\Photos\Cassise Family\
11IMG_2247.jpgIMG_2247.jpg.jpg11/03/2022 08:1012/25/2020 14:4012/25/2020 09:401162570C:\Users\xxxxx\Dropbox\Photos\Cassise Family\
12IMG_2250.jpgIMG_2250.jpg.jpg11/03/2022 08:1012/25/2020 14:4012/25/2020 09:401142768C:\Users\xxxxx\Dropbox\Photos\Cassise Family\
Sheet6

2 Lines of code to bring in the table and set the data type to text
Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Names"]}[Content],
    ChangedType = Table.TransformColumnTypes(Source,{{"Names", type text}})
in
    ChangedType
2 lines of code for the merge
Power Query: 
let
    Source = Table.NestedJoin(Names, {"Names"}, Photos, {"Name"}, "Photos", JoinKind.LeftOuter),
    ExpandedPhotos = Table.ExpandTableColumn(Source, "Photos", {"Name", "Extension", "Date accessed", "Date modified", "Date created", "Size", "Folder Path"}, {"Name", "Extension", "Date accessed", "Date modified", "Date created", "Size", "Folder Path"})
in
    ExpandedPhotos
All code generated by the UI. Change the list and/or the contents of the folder, and a refresh updates the resulting table.
A heck of a lot easier than VBA, and safer.
 
Upvote 0
It is a partial lookup. See post 1.
There are basic names followed by anything.

Maybe a fuzzy merge helps here
 
Upvote 0
JEC said:
It is a partial lookup. See post 1.
There are basic names followed by anything.
Click to expand...
I'll admit it's just out of my reach, but easily done and retains the benefits noted above as well.
 
Upvote 0
jdellasala said:
With a few clicks:
Power Query: 
let
    Source = Folder.Files("C:\Users\jdell\Dropbox\Photos"),
    LowercasedText = Table.TransformColumns(Source,{{"Extension", Text.Lower, type text}}),
    FilteredRowsJPG = Table.SelectRows(LowercasedText, each ([Extension] = ".jpg")),
    RemovedColumns = Table.RemoveColumns(FilteredRowsJPG,{"Content"}),
    ExpandedAttributeSizw = Table.ExpandRecordColumn(RemovedColumns, "Attributes", {"Size"}, {"Size"}),
    ChangedType = Table.TransformColumnTypes(ExpandedAttributeSizw,{{"Size", Int64.Type}})
in
    ChangedType
to get this
Book1
ABCDEFG
1NameExtensionDate accessedDate modifiedDate createdSizeFolder Path
23TimeLoser.jpg.jpg12/27/2022 11:4811/15/2020 18:4411/15/2020 18:44195659C:\Users\xxxxx\Dropbox\Photos\
3AtlantisRollerCoaster600dpi.jpg.jpg12/27/2022 11:4801/09/2020 14:2111/21/2020 16:483369140C:\Users\xxxxx\Dropbox\Photos\
4Blue-nami.jpg.jpg12/27/2022 11:4810/27/2020 18:4810/27/2020 18:48192168C:\Users\xxxxx\Dropbox\Photos\
5Buds.jpg.jpg12/27/2022 11:4802/04/2022 13:1802/04/2022 13:1871207C:\Users\xxxxx\Dropbox\Photos\
6Dad's Obituary.jpg.jpg12/27/2022 11:4802/20/2022 09:4602/20/2022 09:4632227C:\Users\xxxxx\Dropbox\Photos\
7FSTL.jpg.jpg12/27/2022 11:4811/09/2020 16:4811/09/2020 16:4847368C:\Users\xxxxx\Dropbox\Photos\
8FSTL50.jpg.jpg12/27/2022 11:4811/15/2020 15:3511/15/2020 15:3535738C:\Users\xxxxx\Dropbox\Photos\
9FSTL75.jpg.jpg12/27/2022 11:4811/15/2020 15:3511/15/2020 15:3515675C:\Users\xxxxx\Dropbox\Photos\
10Hong Kong Hot Sauce.jpg.jpg12/27/2022 11:4809/14/2020 16:0909/14/2020 16:09352528C:\Users\xxxxx\Dropbox\Photos\
11IMG_20150903_145857.jpg.jpg12/15/2022 10:1809/03/2015 16:0406/30/2020 19:122483059C:\Users\xxxxx\Dropbox\Photos\
12Kyle&Jewel-October 2019.jpg.jpg12/15/2022 10:1812/26/2019 13:0106/30/2020 19:12587572C:\Users\xxxxx\Dropbox\Photos\
13ME-200x231.jpg.jpg11/03/2022 08:1006/13/2010 19:2006/30/2020 19:1210423C:\Users\xxxxx\Dropbox\Photos\
14ME.jpg.jpg11/03/2022 08:1010/08/2010 12:3206/30/2020 19:1231413C:\Users\xxxxx\Dropbox\Photos\
15Mom's Wedding and Honeymoon Announcement.jpg.jpg12/15/2022 10:1802/20/2022 09:3102/20/2022 09:31120201C:\Users\xxxxx\Dropbox\Photos\
16Oliver's Birthday Summer 1999 - 2.jpg.jpg12/15/2022 10:1805/26/2000 13:2106/30/2020 19:1295500C:\Users\xxxxx\Dropbox\Photos\
17Scary.jpg.jpg12/15/2022 10:1806/02/2020 14:2906/30/2020 19:12265974C:\Users\xxxxx\Dropbox\Photos\
18Summer2005.jpg.jpg12/25/2022 17:5309/04/2011 07:0806/30/2020 19:1223369C:\Users\xxxxx\Dropbox\Photos\
Sheet6

Best of all, if files are added, the table can be updated with a quick right click refresh.
None of the code was generated by hand, just the PQ UI.
Just say'n....
Click to expand...
Hi, Thanks for your help.

But am not very used to power query. I watched a tutorial on youtube also. Here the problem is when am pasting your code in vba code is turning red like an error.

Can you help with step to use it
1672655170275.png
 
Upvote 0
Sunil7598 said:
Hi, Thanks for your help.

But am not very used to power query. I watched a tutorial on youtube also. Here the problem is when am pasting your code in vba code is turning red like an error.

Can you help with step to use it
View attachment 81866
Click to expand...
It is NOT VBA code. It's Power Query's M Language. There are great playlists to learn this amazingly powerful function of Excel here and here. You'd be able to get what I did in a few videos. This is a VERY POWERFUL function of Excel, often overlooked or ignored in favor of VBA in these forums when it's clearly the more efficient solution.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
VBA listing folders, sub-folders & file names - modification required
Replies
5
Views
559
NdNoviceHlp
NdNoviceHlp
S
  • Question
Want to rename Jpg files from diff paths
Replies
1
Views
274
ExceLoki
ExceLoki
M
  • Solved
VBA code to get picture dimensions & DPI from selectable folder and it's subfolders
Replies
8
Views
591
NdNoviceHlp
NdNoviceHlp
T
  • Solved
VBA - Image File Name Processing - Change file name, then move to another sheet
Replies
8
Views
434
NdNoviceHlp
NdNoviceHlp
M
  • Question
VBA search for specific file name in folders and their subfolders
Replies
7
Views
1K
Candyman8019
C

Forum statistics

Threads
1,216,096
Messages
6,128,809
Members
449,468
Latest member
AGreen17

Share this page

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