Excel XP VBA code to search all macro code in Excel module for specific search string criteria
 

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:

Excel XP VBA code to search all macro code in Excel module for specific search string criteria
 

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

Excel XP VBA code to search all macro code in Excel module for specific search string criteria
 

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/

Excel XP VBA code to search all macro code in Excel module for specific search string criteria
 

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/

Excel XP VBA code to search all macro code in Excel module for specific search string criteria
 

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