Code that retrieves values from a closed workbook

[Todd Huttenstine]

Hey guys

I believe I picked up this function in this newgroup. I
really cant remember. Anyway what it does is it pulls
data using code (not formula links) from a closed
workbook. Is there anyway that if I new a value in a
closed workbook, that I could search for that in the
closed workbook, and then return the cell address where
the value is located once it found that value? For
example lets say I know the value "Dog" is in Closed
workbook "C:\Test.xls" It is in cell C2. I would want to
run the code from whatever workbook I am in. When I run
it, it returns the value C2. It returns the value C2
because the value "Dog" is located in that cell. Is this
sort of thing possible?

Function GetValue(path, file, sheet, ref)
' Retrieves a value from a closed workbook
Dim arg As String

' Make sure the file exists
If Right(path, 1) <> "\" Then path = path & "\"
If Dir(path & file) = "" Then
GetValue = "File Not Found"
Exit Function
End If

' Create the argument
arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Range("A1").Address(, , xlR1C1)

' Execute an XLM macro
GetValue = ExecuteExcel4Macro(arg)
End Function


Thank you
Todd Huttenstine

 

[Todd Huttenstine]

Actually I used a loop with a counter. Heres what
worked...

Counter = 1
Do
Counter = Counter + 1
p = "Q:\CS Management Reports\Reports Setup"
f = "Authorized List.xls"
s = "Reports Setup"
a = ("B" & Counter)
RemoteValue = GetValue(p, f, s, a)
If NN = RemoteValue Then
MsgBox "MATCH FOUND"
Else
End If
Loop Until Counter = 100


Is there a better way?