Macro to loop in folder and then open the workbook with found value

Hello,

I am a total n00b in VBA

Conditions:

1. There are multiple workbooks in the folder S:\Schedules
2. A fixed cell (G20) in active Workbook shows the Order Number that is currently running.

Task:

Create a macro that loops through S:\Schedules folder, searching for G20 value in the workbooks (without opening them), and then opens the workbook with matching Order (and if possible, highlights the row with that found Order number)

Thanks for any help.

One cannot search a workbook without opening it. Otherwise see if the following helps.

Please Login or Register  to view this content.

Thanks Steve!

I've changed some lines to make it work but your name is credited on the final version

Sub Line_schedule()
'Originated by Steve* on May 2, 2012
Dim sSearchText As String, sPath As String, sFile As String
Dim wb As Workbook, ws As Worksheet, rg As Range

Application.ScreenUpdating = False

'Change Path, need "\"
sPath = "S:\Schedules" & "\"
'Modify "*.xls*" if needed
sFile = Dir(sPath & "*.xls*")

sSearchText = Range("G20")
If Len(sSearchText) = 0 Then Exit Sub
Do While Len(sFile)
If sFile <> ThisWorkbook.Name Then
Set wb = Workbooks.Open(Filename:=sPath & sFile)
If Not wb Is Nothing Then
For Each ws In wb.Worksheets
Set rg = ws.Cells.Find(sSearchText)
If Not rg Is Nothing Then
ws.Activate
rg.Select
Exit Do
End If
Next ws
End If
wb.Close SaveChanges:=False
End If
sFile = Dir()
Loop
Application.ScreenUpdating = True
If wb Is Nothing Then
MsgBox "The string: " & sSearchText & " was not found."
End If
End Sub