Prev: Vba Date$
Next: Declaring Variables
From: Evan on 4 Jun 2010 08:06
anyone know of a search macro or VB script that can perform searches for cell
entrys such as SKUs along multiple Excel spreadsheets? I need a fast way to
search for specific entrys in hundreds of spreadsheets.

Thanks Evan
From: Jackpot on 4 Jun 2010 08:19
If you only require the filename in which the search word is in; then why
dont you try Windows search?

--From windows explorer right click the parent folder.>Search
--From the search window 'All or part of filename' mention *.xl*
--In the secong text box 'A word or phrase in the file' enter your search
keyword and hit 'Search'

"Evan" wrote:

> anyone know of a search macro or VB script that can perform searches for cell
> entrys such as SKUs along multiple Excel spreadsheets? I need a fast way to
> search for specific entrys in hundreds of spreadsheets.
>
> Thanks Evan
From: Jackpot on 4 Jun 2010 09:24
Hi Evan

Try the below in a new workbook.

Sub Macro()
Dim strFile As String, wb As Workbook, ws As Worksheet
Dim strSearch As String, varFound As Variant
Dim intCount As Integer, wsMain As Worksheet

strSearch = InputBox("Enter Search string")
strfolder = "D:\"

Set wsMain = ActiveSheet
strFile = Dir(strfolder & "*.xl*", vbNormal)
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Do While strFile <> ""
Set wb = Workbooks.Open(strfolder & strFile, ReadOnly:=True)
For Each ws In wb.Worksheets
Set varFound = ws.Cells.Find(strSearch, _
LookIn:=xlValues, LookAt:=xlPart)
If Not varFound Is Nothing Then
intCount = intCount + 1
wsMain.Range("A" & intCount) = strFile
wsMain.Range("B" & intCount) = ws.Name
wsMain.Range("C" & intCount) = varFound.Address
wsMain.Range("D" & intCount) = varFound.Text
End If
Next
wb.Close False
Set wb = Nothing
strFile = Dir
Loop
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

"Evan" wrote:

> anyone know of a search macro or VB script that can perform searches for cell
> entrys such as SKUs along multiple Excel spreadsheets? I need a fast way to
> search for specific entrys in hundreds of spreadsheets.
>
> Thanks Evan
 | 
Pages: 1
Prev: Vba Date$
Next: Declaring Variables