Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting data from a closed wbook
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Getting data from a closed wbook | Excel Programming | |||
updating wbook from other... | Excel Programming | |||
transfering data from 2 wbook | Excel Programming | |||
transfering data value from 2 Wbook... | Excel Programming | |||
Read And Write On A Closed Wbook | Excel Programming |