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

[Sunil7598]

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

 

[JEC]

Here a recursive macro. Only change your file path in the first macro.

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

 

[jdellasala]

With a few clicks:

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
	A	B	C	D	E	F	G
1	Name	Extension	Date accessed	Date modified	Date created	Size	Folder Path
2	3TimeLoser.jpg	.jpg	12/27/2022 11:48	11/15/2020 18:44	11/15/2020 18:44	195659	C:\Users\xxxxx\Dropbox\Photos\
3	AtlantisRollerCoaster600dpi.jpg	.jpg	12/27/2022 11:48	01/09/2020 14:21	11/21/2020 16:48	3369140	C:\Users\xxxxx\Dropbox\Photos\
4	Blue-nami.jpg	.jpg	12/27/2022 11:48	10/27/2020 18:48	10/27/2020 18:48	192168	C:\Users\xxxxx\Dropbox\Photos\
5	Buds.jpg	.jpg	12/27/2022 11:48	02/04/2022 13:18	02/04/2022 13:18	71207	C:\Users\xxxxx\Dropbox\Photos\
6	Dad's Obituary.jpg	.jpg	12/27/2022 11:48	02/20/2022 09:46	02/20/2022 09:46	32227	C:\Users\xxxxx\Dropbox\Photos\
7	FSTL.jpg	.jpg	12/27/2022 11:48	11/09/2020 16:48	11/09/2020 16:48	47368	C:\Users\xxxxx\Dropbox\Photos\
8	FSTL50.jpg	.jpg	12/27/2022 11:48	11/15/2020 15:35	11/15/2020 15:35	35738	C:\Users\xxxxx\Dropbox\Photos\
9	FSTL75.jpg	.jpg	12/27/2022 11:48	11/15/2020 15:35	11/15/2020 15:35	15675	C:\Users\xxxxx\Dropbox\Photos\
10	Hong Kong Hot Sauce.jpg	.jpg	12/27/2022 11:48	09/14/2020 16:09	09/14/2020 16:09	352528	C:\Users\xxxxx\Dropbox\Photos\
11	IMG_20150903_145857.jpg	.jpg	12/15/2022 10:18	09/03/2015 16:04	06/30/2020 19:12	2483059	C:\Users\xxxxx\Dropbox\Photos\
12	Kyle&Jewel-October 2019.jpg	.jpg	12/15/2022 10:18	12/26/2019 13:01	06/30/2020 19:12	587572	C:\Users\xxxxx\Dropbox\Photos\
13	ME-200x231.jpg	.jpg	11/03/2022 08:10	06/13/2010 19:20	06/30/2020 19:12	10423	C:\Users\xxxxx\Dropbox\Photos\
14	ME.jpg	.jpg	11/03/2022 08:10	10/08/2010 12:32	06/30/2020 19:12	31413	C:\Users\xxxxx\Dropbox\Photos\
15	Mom's Wedding and Honeymoon Announcement.jpg	.jpg	12/15/2022 10:18	02/20/2022 09:31	02/20/2022 09:31	120201	C:\Users\xxxxx\Dropbox\Photos\
16	Oliver's Birthday Summer 1999 - 2.jpg	.jpg	12/15/2022 10:18	05/26/2000 13:21	06/30/2020 19:12	95500	C:\Users\xxxxx\Dropbox\Photos\
17	Scary.jpg	.jpg	12/15/2022 10:18	06/02/2020 14:29	06/30/2020 19:12	265974	C:\Users\xxxxx\Dropbox\Photos\
18	Summer2005.jpg	.jpg	12/25/2022 17:53	09/04/2011 07:08	06/30/2020 19:12	23369	C:\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....

 

[JEC]

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.

 

[jdellasala]

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.

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
1	Names
2	IMG_1803.jpg
3	IMG_1810.jpg
4	IMG_2024.jpg
5	IMG_2069.jpg
6	IMG_2074.jpg
7	IMG_2077.jpg
8	IMG_2078.jpg
9	IMG_2079.jpg
10	IMG_2215.jpg
11	IMG_2247.jpg
12	IMG_2250.jpg
Sheet6

Merged to this

Book1
	K	L	M	N	O	P	Q	R
1	Names	Name	Extension	Date accessed	Date modified	Date created	Size	Folder Path
2	IMG_1803.jpg	IMG_1803.jpg	.jpg	12/21/2022 04:38	11/20/2020 18:03	11/20/2020 18:03	2929347	C:\Users\xxxxx\Dropbox\Photos\Cassise Family\
3	IMG_1810.jpg	IMG_1810.jpg	.jpg	11/03/2022 08:10	11/20/2020 18:03	11/20/2020 18:03	3096721	C:\Users\xxxxx\Dropbox\Photos\Cassise Family\
4	IMG_2024.jpg	IMG_2024.jpg	.jpg	11/03/2022 08:10	11/26/2020 19:37	11/26/2020 19:37	868582	C:\Users\xxxxx\Dropbox\Photos\Cassise Family\
5	IMG_2069.jpg	IMG_2069.jpg	.jpg	12/06/2022 06:18	12/01/2020 17:28	12/01/2020 17:28	1665068	C:\Users\xxxxx\Dropbox\Photos\Cassise Family\
6	IMG_2074.jpg	IMG_2074.jpg	.jpg	11/03/2022 08:10	12/01/2020 17:27	12/01/2020 17:27	1528637	C:\Users\xxxxx\Dropbox\Photos\Cassise Family\
7	IMG_2077.jpg	IMG_2077.jpg	.jpg	12/26/2022 00:06	12/01/2020 17:27	12/01/2020 17:27	1387866	C:\Users\xxxxx\Dropbox\Photos\Cassise Family\
8	IMG_2078.jpg	IMG_2078.jpg	.jpg	12/27/2022 21:50	12/01/2020 17:28	12/01/2020 17:28	1459815	C:\Users\xxxxx\Dropbox\Photos\Cassise Family\
9	IMG_2079.jpg	IMG_2079.jpg	.jpg	11/03/2022 08:10	12/01/2020 17:27	12/01/2020 17:27	1660883	C:\Users\xxxxx\Dropbox\Photos\Cassise Family\
10	IMG_2215.jpg	IMG_2215.jpg	.jpg	12/16/2022 09:24	12/25/2020 14:40	12/25/2020 09:40	2247995	C:\Users\xxxxx\Dropbox\Photos\Cassise Family\
11	IMG_2247.jpg	IMG_2247.jpg	.jpg	11/03/2022 08:10	12/25/2020 14:40	12/25/2020 09:40	1162570	C:\Users\xxxxx\Dropbox\Photos\Cassise Family\
12	IMG_2250.jpg	IMG_2250.jpg	.jpg	11/03/2022 08:10	12/25/2020 14:40	12/25/2020 09:40	1142768	C:\Users\xxxxx\Dropbox\Photos\Cassise Family\
Sheet6

2 Lines of code to bring in the table and set the data type to text

let
    Source = Excel.CurrentWorkbook(){[Name="Names"]}[Content],
    ChangedType = Table.TransformColumnTypes(Source,{{"Names", type text}})
in
    ChangedType

2 lines of code for the merge

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.

 

[JEC]

It is a partial lookup. See post 1.
There are basic names followed by anything.

Maybe a fuzzy merge helps here

 

[jdellasala]

It is a partial lookup. See post 1.
There are basic names followed by anything.

I'll admit it's just out of my reach, but easily done and retains the benefits noted above as well.

 

[Sunil7598]

With a few clicks:

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
	A	B	C	D	E	F	G
1	Name	Extension	Date accessed	Date modified	Date created	Size	Folder Path
2	3TimeLoser.jpg	.jpg	12/27/2022 11:48	11/15/2020 18:44	11/15/2020 18:44	195659	C:\Users\xxxxx\Dropbox\Photos\
3	AtlantisRollerCoaster600dpi.jpg	.jpg	12/27/2022 11:48	01/09/2020 14:21	11/21/2020 16:48	3369140	C:\Users\xxxxx\Dropbox\Photos\
4	Blue-nami.jpg	.jpg	12/27/2022 11:48	10/27/2020 18:48	10/27/2020 18:48	192168	C:\Users\xxxxx\Dropbox\Photos\
5	Buds.jpg	.jpg	12/27/2022 11:48	02/04/2022 13:18	02/04/2022 13:18	71207	C:\Users\xxxxx\Dropbox\Photos\
6	Dad's Obituary.jpg	.jpg	12/27/2022 11:48	02/20/2022 09:46	02/20/2022 09:46	32227	C:\Users\xxxxx\Dropbox\Photos\
7	FSTL.jpg	.jpg	12/27/2022 11:48	11/09/2020 16:48	11/09/2020 16:48	47368	C:\Users\xxxxx\Dropbox\Photos\
8	FSTL50.jpg	.jpg	12/27/2022 11:48	11/15/2020 15:35	11/15/2020 15:35	35738	C:\Users\xxxxx\Dropbox\Photos\
9	FSTL75.jpg	.jpg	12/27/2022 11:48	11/15/2020 15:35	11/15/2020 15:35	15675	C:\Users\xxxxx\Dropbox\Photos\
10	Hong Kong Hot Sauce.jpg	.jpg	12/27/2022 11:48	09/14/2020 16:09	09/14/2020 16:09	352528	C:\Users\xxxxx\Dropbox\Photos\
11	IMG_20150903_145857.jpg	.jpg	12/15/2022 10:18	09/03/2015 16:04	06/30/2020 19:12	2483059	C:\Users\xxxxx\Dropbox\Photos\
12	Kyle&Jewel-October 2019.jpg	.jpg	12/15/2022 10:18	12/26/2019 13:01	06/30/2020 19:12	587572	C:\Users\xxxxx\Dropbox\Photos\
13	ME-200x231.jpg	.jpg	11/03/2022 08:10	06/13/2010 19:20	06/30/2020 19:12	10423	C:\Users\xxxxx\Dropbox\Photos\
14	ME.jpg	.jpg	11/03/2022 08:10	10/08/2010 12:32	06/30/2020 19:12	31413	C:\Users\xxxxx\Dropbox\Photos\
15	Mom's Wedding and Honeymoon Announcement.jpg	.jpg	12/15/2022 10:18	02/20/2022 09:31	02/20/2022 09:31	120201	C:\Users\xxxxx\Dropbox\Photos\
16	Oliver's Birthday Summer 1999 - 2.jpg	.jpg	12/15/2022 10:18	05/26/2000 13:21	06/30/2020 19:12	95500	C:\Users\xxxxx\Dropbox\Photos\
17	Scary.jpg	.jpg	12/15/2022 10:18	06/02/2020 14:29	06/30/2020 19:12	265974	C:\Users\xxxxx\Dropbox\Photos\
18	Summer2005.jpg	.jpg	12/25/2022 17:53	09/04/2011 07:08	06/30/2020 19:12	23369	C:\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....

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

 

[jdellasala]

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

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.