Getting data from a closed wbook

[caroline]

Hello,
Getting data from a closed wbook
it works very well with the CODE 1 below inspired from
http://www.rondebruin.nl/copy7.htm
However, I need to extract data from a cell which address varies per sheet
and has not been named. So I would have expected to use an offset function
from a named cell (see CODE 2 (below)but it does not work. It returns a
#VALUE!
any help would be greatly appreciated
Thanks a million
Caroline


CODE 1:
For i = 1 To N
Dim Name As String
Name = Range("WorksheetName1").Offset(i - 1, 0).Value

Dim mypath As String
mypath = "='" & Range("mypath").Value & "\" & "[" &
Range("WorkbookName").Value & "]" & Range("WorksheetName1").Offset(i - 1,
0).Value & "'!"

'export data
Dim CalcExpt(1 To 3) As String
'DATES
CalcExpt(1) = "YearEnd"
CalcExpt(2) = "Launchyear1"
CalcExpt(3) = "Launchyear2"

'import range
Dim CalcImpt(1 To 3) As Range
'DATES
Set CalcImpt(1) = Sheets(Name).Range("YearEnd")
Set CalcImpt(2) = Sheets(Name).Range("Launchyear1")
Set CalcImpt(3) = Sheets(Name).Range("Launchyear2")
'copy data
For j = 1 To UBound(CalcExpt)

Dim mydata As String
mydata = mypath & CalcExpt(j)

With CalcImpt(j)
.FormulaArray = mydata
.Value = .Value
End With
Next

Next


CODE 2
For i = 1 To N
Dim Name As String
Name = Range("WorksheetName1").Offset(i - 1, 0).Value

Dim mypath As String
mypath = "=OFFSET('" & Range("mypath").Value & "\" & "[" &
Range("WorkbookName").Value & "]" & Range("WorksheetName1").Offset(i - 1,
0).Value & "'!"
Dim str As String
str = ",-1,-1)"

'export data
Dim CalcExpt(1 To 1) As String
CalcExpt(1) = "Step2Cell1"

Dim CalcImpt(1 To 1) As Range
Set CalcImpt(1) = Sheets(Name).Range("Step2Cell1")

For j = 1 To UBound(CalcExpt)

Dim mydata As String
mydata = mypath & CalcExpt(j) & str

With CalcImpt(j)
.FormulaArray = mydata
.Value = .Value
End With
Next
Next