Geoff,

Use a helper cell in the closed workbook and add formula like this:

=COUNTA(A:A)

This should give you the total number of rows

You can then use following procedure to copy all the data from required
sheet / range in closed workbook using formula. If you use a hidden sheet to
store this data your lookup formula can then reference the active workbook.

May need some work but hope gives you some ideas.


Sub GetData()
Dim mydata As String
Dim rcount As String
Dim lr As Long

'helper cell
rcount = "='C:\[MyTestBook.xls]Sheet1'!$C$1"

'link to worksheet
With ThisWorkbook.Worksheets(1)

With .Range("C1")

.Formula = rcount

'convert formula to text

.Value = .Value

lr = .Value

End With

'data location & range to copy
mydata = "='C:\[MyTestBook.xls]Sheet1'!$A$1:$A$" & lr

With .Range("A1:A" & lr)
.Formula = mydata

'convert formula to text

.Value = .Value

End With

End With

End Sub

--
jb


"Geoff K" wrote:

Hi
Getting data from a closed wbook.
These wsheet formulae work fine on numerical fields but not on text.

Gets from a closed wbook the value in the last used cell of a column.
=LOOKUP(99^99,'C:\Path\[File.xls]Sheet1'!A:A)

Gets from a closed wbook the last used row number of a column.
=MATCH(99^99,'C:\Path\[File.xls]Sheet1'!A:A)

But if either meet with a text field or even if 99^99 is substituted with
"ZZZ" then Excel goes into an infinite loop.

How can the formulae be made universal to look for either numeric or text
fields? Or if that is not possible then how might it made to work in a text
field?

T.I.A

Geoff