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



## Sunil7598 (Dec 28, 2022)

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


----------



## jdellasala (Dec 28, 2022)

Open a Command Prompt, change to the Desktop (*CD Desktop*), and type the command 
*DIR "C:\folder\subfolder\37004378_TM*.jpg" /B > JPGFiles.txt*
change "folder" and "subfolder" as needed. If there are more subfolders add */S* after /B. This will create a text file with all the jpg files that start with 37004378_TM with the complete path.
You can pull the file into Excel with a simple File Open which will start the Text Import wizard, or with Power Query where it would be easier to remove the full path from the file names.


----------



## JEC (Dec 28, 2022)

Try to run this one with te correct folder. Go for the high level folder. It will search in all subfolders


```
Sub jec()
Dim xp As String, a As Variant
xp = "C:\Users\xxxx\Documents\37004378_TM*.jpg"

a = Split(CreateObject("wscript.shell").exec("cmd /c dir """ & xp & """ /b/s").stdout.readall, vbCrLf)
Cells(1).Resize(UBound(a)) = Application.Transpose(a)
End Sub
```


----------



## Sunil7598 (Dec 28, 2022)

JEC said:


> Sub jec() Dim xp As String, a As Variant xp = "C:\Users\xxxx\Documents\37004378_TM*.jpg" a = Split(CreateObject("wscript.shell").exec("cmd /c dir """ & xp & """ /b/s").stdout.readall, vbCrLf) Cells(1).Resize(UBound(a)) = Application.Transpose(a) End Sub


Thankyou JEC

it work's perfectly


----------



## JEC (Dec 28, 2022)

You're welcome!


----------



## Sunil7598 (Dec 29, 2022)

JEC said:


> You're welcome!


Hi JEC,

but works very slow I have around 150 number of images list. It would take a lot of time. Is there any other way of doing it


----------



## JEC (Dec 29, 2022)

Searching through a lot of subfolders is not very fast. There is another approach, using a recursive macro.


----------



## Sunil7598 (Dec 29, 2022)

JEC said:


> recursive macro


fine.

But it's not working with variable. as I said it has 150 lines of images


Dim TargetAddress As String, lr As Long
Dim xp As String, C As Variant

lr = Cells(Rows.Count, 2).End(xlUp).Row

For i = 2 To lr

TargetAddress = Sheets("Data2").Cells(i, 2).Value
TargetAddress = TargetAddress & "*"
xp = "\\arlmssan02\COM_Mumbai$\activecell.value*.jpg"

C = Split(CreateObject("wscript.shell").exec("cmd /c dir """ & xp & """ /b/s").StdOut.ReadAll, vbCrLf)
Cells(18).Resize(UBound(C)) = Application.Transpose(C)
'Cells(1).Resize(UBound(a)) = Application.Transpose(a)
Next i
End Sub

Am trying this. please help me


----------



## JEC (Dec 29, 2022)

I don’t see how you use your loop here.
This just does the thing x times over and over. Depending on the value of “lr”

Or do you want to use the “targetaddress” variable in your path?


----------



## Sunil7598 (Dec 29, 2022)

Dim xp As String, C As Variant
Dim TargetAddress As String, lr As Long


lr = Cells(Rows.Count, 2).End(xlUp).Row

For i = 2 To lr

TargetAddress = Sheets("Data2").Cells(i, 2).Value
TargetAddress = TargetAddress & "*"
xp = "\\arlmssan02\COM_Mumbai$\TargetAddress*.jpg"

C = Split(CreateObject("wscript.shell").exec("cmd /c dir """ & xp & """ /b/s").StdOut.ReadAll, vbCrLf)
Cells(18).Resize(UBound(C)) = Application.Transpose(C)
'Cells(1).Resize(UBound(a)) = Application.Transpose(a)
Next i
End Sub

Target address is jpg file name I have to search it changes as per loop. I changed a bit to check with activecell.value


JEC said:


> I don’t see how you use your loop here.
> This just does the thing x times over and over. Depending on the value of “lr”
> 
> Or do you want to use the “targetaddress” variable in your path?


----------



## Sunil7598 (Dec 28, 2022)

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 (Dec 31, 2022)

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 (Dec 31, 2022)

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
Book1ABCDEFG1NameExtensionDate accessedDate modifiedDate createdSizeFolder Path23TimeLoser.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....


----------



## JEC (Dec 31, 2022)

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 (Dec 31, 2022)

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.


You're kidding, right?

Load table of names desired into PQ.
Merge names table with the folder to a new query
Load table.
Book1I1Names2IMG_1803.jpg3IMG_1810.jpg4IMG_2024.jpg5IMG_2069.jpg6IMG_2074.jpg7IMG_2077.jpg8IMG_2078.jpg9IMG_2079.jpg10IMG_2215.jpg11IMG_2247.jpg12IMG_2250.jpgSheet6
Merged to this
Book1KLMNOPQR1NamesNameExtensionDate accessedDate modifiedDate createdSizeFolder Path2IMG_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

```
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 (Dec 31, 2022)

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

Maybe a fuzzy merge helps here


----------



## jdellasala (Dec 31, 2022)

JEC said:


> 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 (Jan 2, 2023)

jdellasala said:


> With a few clicks:
> 
> ```
> let
> ...


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 (Jan 2, 2023)

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.
> 
> ...


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.


----------

