You might try recording a macro using File:Open, Tools, Find, and setting
your target folder, file type, and Text or Property values.

Ed

"Roger1947" wrote in message
...
Hi all,

I am looking to write a Excel XP vba code which will allow me to search
for a specific search string in a Excel macro module for all the "xls"
spreadsheet type files in the folder location which will prompt me which
could be "C:\" .

For example..I want to search all the spreadsheets for the string
"C:\temp" hardcoded into the Excel XP macro module using an automated
solution like Excel XP VBA.

I am using Excel XP on Windows XP o/s.

Please help me out with the code as I have to present a report to my
manager in 2 days.

Thanks,
Roger.
E-mail:

Thanks Ed,
For your offer to help.
I tried doing it with your suggestion but without success.

Thanks,
Roger
E-mail:

--
Message posted from http://www.ExcelForum.com

Look in VBA Help at the FileSearch object. (For VBA Help, launch Excel, hit
Alt+F11, then F1.)

Additionally, go to http://www.rondebruin.nl/Google.htm and download the
add-in. Use it to search the newsgroups for your questions. Lots of this
stuff has been answered several times before.

Ed

"Roger1947 " wrote in message
...
Thanks Ed,
For your offer to help.
I tried doing it with your suggestion but without success.

Thanks,
Roger
E-mail:


---
Message posted from http://www.ExcelForum.com/

Raj, I have a special purpose macro that will do what you are after. I will
send you an e-mail shortly.

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

"Roger1947 " wrote in message
...
Thanks Ed,
For your offer to help.
I tried doing it with your suggestion but without success.

Thanks,
Roger
E-mail:


---
Message posted from http://www.ExcelForum.com/

Try the code below. Provide the string to find and folder name:

Sub StringSearch()

Dim wb As Object
Dim strText As String

strText = "............." 'string to find

With Application.FileSearch
.NewSearch
.LookIn = "C:\........" 'folder name
.SearchSubFolders = True
.FileType = msoFileTypeExcelWorkbooks
End With

With Application.FileSearch
If .Execute() 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
Application.DisplayAlerts = False
For i = 1 To .FoundFiles.Count
Set wb = Workbooks.Open(Filename:=.FoundFiles(i))
If FindString(strText, wb) = True Then
MsgBox "Found " & strText & " in "
.FoundFiles(i)
End If
wb.Close
Set wb = Nothing
Next i
Application.DisplayAlerts = True
Else
MsgBox "There were no files found."
End If
End With

End Sub

Function FindString(strText, wb) As Boolean

Dim i As Integer
Dim z As Long

'The following example uses the Find method to verify that the
'specified block of lines, lines 1261 through 1279,
'of a particular code pane does contain the string "Tabs.Clear."

'.....CodeModule.Find (text to find, start line, start position
end line, end line position, False, False)


On Error GoTo FindError

For i = 1 To Application.VBE.VBProjects.Count
If InStr(1, Application.VBE.VBProjects(i).Filename, wb.Name)
0 Then
For m = 1 T
Application.VBE.VBProjects(i).VBComponents.Count
With Application.VBE.VBProjects(i).VBComponents(m)
If .CodeModule.Find(strText, z, 1, z + 1, 1, False
False) Then
FindString = True
Exit Function
End If
End With
Next m
End If
Next i
Exit Function

FindError:

If Err.Number = 50289 Then 'file is password protected
MsgBox wb.Name & " is protected and cannot be scanned
Continuing.."
FindString = False
Exit Function
End If


End Functio

--
Message posted from http://www.ExcelForum.com