Hi Terry

Assume you wish to retrieve data into sheet "GetData" of the active workbook
from a sheet called "MySheet" in a closed workbook called "Tester.xls"
(which also has a sheet called "MyRange").

Firstly, I'd place the following code as a "Workbook Close" event in
"Tester.xls".
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("MyRange").Range("A1") = Sheets("MySheet").UsedRange.Address
End Sub


Then, to extract the data from "MySheet" into the active workbook...

Sub Retrieve()
Dim cellrange As String, fpath As String, fname As String
Dim sname2 As String, sname As String, s As String
Dim srange As Range

fpath = "C:\~MS\WORK"
fname = "Tester.xls"
sname = "MySheet"
sname2 = "MyRange"
cellrange = "A1"

With ThisWorkbook.Sheets("GetData")
'Identify the range to be used
..Range("A1").Formula = "='" & fpath & "\[" & fname & "]" & sname2 & "'!" &
cellrange
..Range("A1").Formula = .Range("A1").Value

'Import the data
Set srange = .Range(.Range("A1"))
s = .Range("A1")
srange.FormulaArray = "='" & fpath & "\[" & fname & "]" & sname & "'!" & s

'To convert formulae to values
'srange.Formula = srange.Value2
End With

End Sub



--
XL2002
Regards

William


"Terry VanDuzee" wrote in message
...
| Hello
| How can I get the usedrange from a sheet in a closed workbook?
|
| This is how I am getting the values:
|
| With ActiveSheet.Range(cellrange)
| .FormulaArray = "='" & fpath & "\[" & fname & "]" _
| & sname & "'!" & cellrange
| End With
|
| Where cellrange right now is a variable that contains a range that is
| hardcoded. I want to be able to find the usedrange in the closed
workbook,
| to pass it as a variable to this sub from the calling sub.
|
|
| Thank you
| Terry V
|
|