C
caroline
Hello,
I tried to post this question a few days ago but did not get any answer. I
am still stuck. So i try again
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
I tried to post this question a few days ago but did not get any answer. I
am still stuck. So i try again
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