Take a look at this blog entry:
http://www.dicks-blog.com/archives/2...ing-workbooks/
I was able to create a function to return a single cell's value using
information from that article. For example:
Private Function GetXLSData(sFile As String, sSheet As String, sCell As
String)
Set oDB = CreateObject("ADODB.Recordset")
sConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sFile & ";" & _
"Extended Properties='Excel 8.0;HDR=No'"
sSQL = "SELECT * From [" & sSheet & "$" & sCell & ":" & sCell & "]"
oDB.Open sSQL, sConn, 3, 3, 1
GetXLSData = oDB.Fields.Item(0).Value
oDB.Close
Set oDB = Nothing
End Function
Then, my formula was:
=GetXLSData("Lists.xls", "Sheet1","B2")