calculate a formula instead of returning text

O.K. Here's code which will extract data from a specified cell in a
closed file. Many thanks to Randy Harmelink:

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, his formula is:
=GetXLSData("Lists.xls", "Sheet1","B2")

Refer:

http://www.excelforum.com/showthread.php?t=531126

http://tinyurl.com/jvrwv


--
John James
------------------------------------------------------------------------
John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=527258

John James wrote...
O.K. Here's code which will extract data from a specified cell in a
closed file. Many thanks to Randy Harmelink:

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, his formula is:
=GetXLSData("Lists.xls", "Sheet1","B2")
....

Several caveats. Most significant, the ADO DLL must be installed. Not
sure if this it's installed by default by (or even included with)
Standard versions of Office or standalone Excel. Second, this works for
single cell sCell, but not for multiple cell ranges, so limited
functionality.

Other alternatives are given in the following article in the archives.

http://groups.google.com/group/micro...443753560f0075

(or http://makeashorterlink.com/?B34B15DCC ).