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

 

[jdellasala]

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]

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]

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]

You're welcome!

 

[Sunil7598]

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]

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

 

[Sunil7598]

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]

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]

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

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?