Way to Many Spead Sheets

[Greg]

I have currently 325 Spread sheets showing shipping data.. I got asked to find one piece of data from a job that they can not remember where or how etc.. Is there a way to batch search all my spreadsheets to find this one piece, (a text value) with out having to open up every spread sheet and doing a find?
thanks

 

[Matanuska]

Try doing this outside of Excel using Find / Files or Folders on the Start Menu.

Restrict the search to the folder containing the worksheets using the Browse button on the Name & Location tab.

Enter the text string you're looking for in the "Containing text:" box and search.

 

[AJ]

Alternatively, if you do decide you want to open all these spreadsheets, I've found the code below useful in the past.

It goes thorough every Excel file in a directory you specify, does something to it (you have to put that bit in yourself), then closes it and moves it to a "Processed" directory off the path you started from.

--
Sub movestuff()

'Change the drive to whatever drive you're on
ChDrive "C"


'Define Path
MyPath = "C:temp"

'Change directory to your directory
ChDir (MyPath)

' See if a subdirectoy called "Processed" exists
MyCheckDir = Dir(MyPath & "Processed", vbDirectory)

'If it doesn't exist then create it
If MyCheckDir = "" Then
MkDir MyPath & "Processed"
End If

' Now go looking for Excel files to process
ChDir MyPath & ""
MyFile = Dir("*.xls", vbNormal)

Do While MyFile<> ""

Workbooks.Open Filename:=MyFile

'Do your stuff here...




'Switch back to the open Excel file and close it, without saving
Windows(MyFile).Activate
ActiveWorkbook.Close SaveChanges:=0

'Copy the file we've processed to the Processed Directory
FileCopy MyPath & "" & MyFile, MyPath & "Processed" & MyFile
'Delete original
Kill MyFile

' Call Dir again without arguments to return the next *.XLS file in
' the same directory.
MyFile = Dir


Loop
End Sub

--

Hope this helps.
Rgds
AJ



Please note, Where ever you see two backslashes together above, only put one in your code.
This message was edited by AJ on 2002-03-06 07:48