macro & folders

  • Thread starter Thread starter Mac123
  • Start date Start date
M

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
 
Does the filename *exactly* match the product name ?
Are the file just randomly distributed in the 1000 folders, or is there some
system ?

Tim
 
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
 
Back
Top