macro & folders

[Mac123]

Hi,
I have got a list(thousands) products that I need to look up from thousand
separate folders located in a different drive e.g P/drive. For example I need
to find whether on not product items l41 or m42 are in the folders. So I need
a macro that tells me whether the product item I’m looking for is in the
folders rather than manually search.
Thanks, Mac

 

[Tim Williams]

What does a "product" look like ? Is it an Excel file or what ?

Tim

 

[Mac123]

Yes, it is excel file.

What does a "product" look like ? Is it an Excel file or what ?

Tim

 

[Tim Williams]

Does the filename *exactly* match the product name ?
Are the file just randomly distributed in the 1000 folders, or is there some
system ?

Tim

 

[Mac123]

Hi,
Yes it does exactly match and they are randomly distributed in the 1000s
folders.

 

[Tim Williams]

Mac,

You can use the FileSystemObject to search through the folders and list all
the files.
Then use vlookup against the list. It's not "real time" but should be close
enough.

You'll need to add a reference in the VBA editor
Tools > References
Check "Microsoft srcipting runtime"

Tim


'****************************************
Option Explicit

Dim FSO As Scripting.FileSystemObject


Sub Tester()
ListFiles "D:\Analysis", 2
End Sub


Sub ListFiles(FolderPath, RowNum)
Dim f As Object, sf As Object, fold As Object

If FSO Is Nothing Then
Set FSO = New Scripting.FileSystemObject
End If

Set fold = FSO.GetFolder(FolderPath)
For Each f In fold.Files
If f.Name Like "*.xls" Then
With ThisWorkbook.Sheets("Files").Rows(RowNum)
.Cells(1).Value = FSO.GetBaseName(f)
.Cells(2).Value = f.Path
End With
RowNum = RowNum + 1
End If
Next f

For Each sf In fold.SubFolders
ListFiles sf.Path, RowNum
Next sf

End Sub

'****************************************


Tim

 

[Mac123]

Thanks Tim, will try that and let you know, looks like it will work.