'find' in directory
 

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

'find' in directory
 

Ron de Bruin has examples of cycling through files in a directory.

http://www.rondebruin.nl/copy3.htm
http://www.rondebruin.nl/copy33.htm


HTH
Paul
--------------------------------------------------------------------------------------------------------------
Be advised to back up your WorkBook before attempting to make changes.
--------------------------------------------------------------------------------------------------------------

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

'find' in directory
 

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

-----Original Message-----
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."
.