'find' in directory

  • Thread starter Thread starter dave smith
  • Start date Start date
D

dave smith

I'm trying to search for a unique reference number in one
workbook in an archive directory. eg. one cell
containing - "03/405/ful", which could be located in one
of several hundred workbooks of archived files.

i can search the individual workbooks fine, but what
would the best way be to open up each file in the
directory (each with no predictable/regular name) and
search through them individually?

I guess, to summarise, "how would i open every file in a
folder one by one, when I don't know what name they will
have."
 
Dave,

The follwoing sample code might help you:

Sub All_Speadsheets()
Dim fs, f, f1
fldr = "C:\My Documents\"
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(fldr)
Set f1 = f.files
For Each f2 In f1
filenam = fldr & f2.Name
If Right(filenam, 4) = ".xls" Then
Workbooks.Open filenam
'code to do what you want
ActiveWorkbook.Close
End If
Next
End Sub

HTH,
Nikos
 
Back
Top