Way to Many Spead Sheets

Greg · Mar 6th, 2002, 08:10 AM

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 · Mar 6th, 2002, 08:41 AM

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 · Mar 6th, 2002, 08:47 AM

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 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 6th, 2002, 08:10 AM	#1
Greg New Member Join Date: Mar 2002 Posts: 1	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

Mar 6th, 2002, 08:41 AM	#2
Matanuska New Member Join Date: Feb 2002 Location: Houston, Texas Posts: 28	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.

Mar 6th, 2002, 08:47 AM	#3
AJ Board Regular Join Date: Mar 2002 Location: =ActiveCell.Address Posts: 478	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 ]