calculate a formula instead of returning text

J

John James

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

Private Function GetXLSData(sFile As String, sSheet As String, sCel
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/jvrw
 
H

Harlan Grove

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")
Click to expand...
....

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/microsoft.public.excel.worksheet.functions/msg/ac443753560f0075

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