Find values in a closed excel workbook.

[Guest]

hey all,

GOt the below script working to search for people in a list. However i would
also like this to search throgh other excel sheets on a network drive which
are not opened at the moment. Is there a easy way of doing this. If anyone
does provide code please put it around what i have as i have dyslexia and
cannot see how it works sometimes.... Thanks in advance. Any input on how it
works would be greatly beneficial....


Dim AddrList(60000) As String
Dim n As Long, i As Long, c As Range, firstaddress As String
Dim Searchvalue As String

Searchvalue = UserForm1.TextBox_Employee.Value

n = 0
With Workbooks("database.xls").Worksheets("users").Range("g:g")
Set c = .Find(Searchvalue, LookIn:=xlValues)
If Not c Is Nothing Then
firstaddress = c.Address
Do
n = n + 1
AddrList(n) = c.Offset(, -5).Value
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstaddress
End If
End With

For i = 1 To n
UserForm2.ListBox1.AddItem AddrList(i)
Next i

UserForm2.Show
End Sub

 

[merjet]

JE McGimpsey shows how to get a value from a closed workbook here:
http://www.mcgimpsey.com/macoffice/excel/getcellvalue.html
if you know exactly what cell has the value. Since you want to do a
search, I believe you will have to open the workbook.

Hth,
Merjet