I would like to retrieve the value in a named cell in a closed workbook. The
workbook name, sheet name, and range name are known.

Without opening the workbook, is this possible?

Thanks.

JOhn Wirt

John,

here is one way using ADO

Dim oRS As Object
Dim sConnect As String
Dim sSQL As String
Dim sFile As String
Dim oOrig As Worksheet
Dim osh As Worksheet
Dim oCell As Range
Dim aryData

sFile = "C:\myTest\Volker1.xls"

' Create the connection string.
sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sFile & ";" & _
"Extended Properties=Excel 8.0;"

' Query based on the worksheet name.
sSQL = "SELECT * FROM [Sheet1$]"

Set oRS = CreateObject("ADODB.Recordset")
oRS.Open sSQL, sConnect, 0, 1, 1

If Not oRS.EOF Then
aryData = oRS.getrows()
Else
MsgBox "No records returned.", vbCritical
End If

MsgBox "Cell A2 contains " & aryData(0, 0)
MsgBox "Cell D4 contains " & aryData(3, 2)

' Clean up our Recordset object.
oRS.Close
Set oRS = Nothing


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"John Wirt" wrote in message
...
I would like to retrieve the value in a named cell in a closed workbook.
The
workbook name, sheet name, and range name are known.

Without opening the workbook, is this possible?

Thanks.

JOhn Wirt

"Bob Phillips" wrote in message
...
John,

here is one way using ADO

Dim oRS As Object
Dim sConnect As String
Dim sSQL As String
Dim sFile As String
Dim oOrig As Worksheet
Dim osh As Worksheet
Dim oCell As Range
Dim aryData

sFile = "C:\myTest\Volker1.xls"

' Create the connection string.
sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sFile & ";" & _
"Extended Properties=Excel 8.0;"

' Query based on the worksheet name.
sSQL = "SELECT * FROM [Sheet1$]"

Set oRS = CreateObject("ADODB.Recordset")
oRS.Open sSQL, sConnect, 0, 1, 1

If Not oRS.EOF Then
aryData = oRS.getrows()
Else
MsgBox "No records returned.", vbCritical
End If

MsgBox "Cell A2 contains " & aryData(0, 0)
MsgBox "Cell D4 contains " & aryData(3, 2)

' Clean up our Recordset object.
oRS.Close
Set oRS = Nothing


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"John Wirt" wrote in message
...
I would like to retrieve the value in a named cell in a closed workbook.
The
workbook name, sheet name, and range name are known.

Without opening the workbook, is this possible?

Thanks.

JOhn Wirt