Find values in a closed excel workbook.

S

Sjakkie

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top