Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting data from a closed wbook (adaptation of rondebruin's)
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting data from a closed wbook (adaptation of rondebruin's)
Hello Caroline,
It's very difficult to recreate the your code. Start by describing what you have, sheet names, workbook names and full file names etc. Post your actual code and give details about anything else we need to know. Eg presumably "WorksheetName1" is a worksheet level name on the active sheet, what does it refer to and what do the referenced cells contain, Names I guess What line gives the Value error Regards, Peter T "caroline" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting data from a closed wbook (adaptation of rondebruin's)
hello,
Thank you very much for answering. I was away from my computer. I see where the confusion comes from as names are the same on the closed workbook where the data is imported from and open workbook where the data is imported to. mypath, WorkbookName,WorksheetName1 are named cells that described where the data is imported from YearEnd,Launchyear1, Launchyear2 are named cells that represent the data to import (they are named cells on the workbook that is closed) Worksheetname1 is also the name of the sheet of the open workbook where the data is imported to, and YearEnd,Launchyear1, Launchyear2 are also on the open workbook That code (code 1)works perfectly The second one gives the error because I am using offset. I am trying to create a formulae to import from a cell that has not been named and changed its address depending on the worksheet it is in. I am not sure I am much clearer. I appreciate your help. thanks -- caroline "Peter T" wrote: Hello Caroline, It's very difficult to recreate the your code. Start by describing what you have, sheet names, workbook names and full file names etc. Post your actual code and give details about anything else we need to know. Eg presumably "WorksheetName1" is a worksheet level name on the active sheet, what does it refer to and what do the referenced cells contain, Names I guess What line gives the Value error Regards, Peter T "caroline" wrote in message ... 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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting data from a closed wbook (adaptation of rondebruin's)
Sorry I really can't follow what you've got in order to recreate it, in
particular myPath in your Code2. Maybe if you debug the formula whatever is wrong will become clear. Debug.Print myPath Paste the result into a cell, look at each section of the formula in edit mode Regards, Peter T "caroline" wrote in message ... hello, Thank you very much for answering. I was away from my computer. I see where the confusion comes from as names are the same on the closed workbook where the data is imported from and open workbook where the data is imported to. mypath, WorkbookName,WorksheetName1 are named cells that described where the data is imported from YearEnd,Launchyear1, Launchyear2 are named cells that represent the data to import (they are named cells on the workbook that is closed) Worksheetname1 is also the name of the sheet of the open workbook where the data is imported to, and YearEnd,Launchyear1, Launchyear2 are also on the open workbook That code (code 1)works perfectly The second one gives the error because I am using offset. I am trying to create a formulae to import from a cell that has not been named and changed its address depending on the worksheet it is in. I am not sure I am much clearer. I appreciate your help. thanks -- caroline "Peter T" wrote: Hello Caroline, It's very difficult to recreate the your code. Start by describing what you have, sheet names, workbook names and full file names etc. Post your actual code and give details about anything else we need to know. Eg presumably "WorksheetName1" is a worksheet level name on the active sheet, what does it refer to and what do the referenced cells contain, Names I guess What line gives the Value error Regards, Peter T "caroline" wrote in message ... 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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting data from a closed wbook (adaptation of rondebruin's)
Thanks Peter will do. I appreciated your help.
-- caroline "Peter T" wrote: Sorry I really can't follow what you've got in order to recreate it, in particular myPath in your Code2. Maybe if you debug the formula whatever is wrong will become clear. Debug.Print myPath Paste the result into a cell, look at each section of the formula in edit mode Regards, Peter T "caroline" wrote in message ... hello, Thank you very much for answering. I was away from my computer. I see where the confusion comes from as names are the same on the closed workbook where the data is imported from and open workbook where the data is imported to. mypath, WorkbookName,WorksheetName1 are named cells that described where the data is imported from YearEnd,Launchyear1, Launchyear2 are named cells that represent the data to import (they are named cells on the workbook that is closed) Worksheetname1 is also the name of the sheet of the open workbook where the data is imported to, and YearEnd,Launchyear1, Launchyear2 are also on the open workbook That code (code 1)works perfectly The second one gives the error because I am using offset. I am trying to create a formulae to import from a cell that has not been named and changed its address depending on the worksheet it is in. I am not sure I am much clearer. I appreciate your help. thanks -- caroline "Peter T" wrote: Hello Caroline, It's very difficult to recreate the your code. Start by describing what you have, sheet names, workbook names and full file names etc. Post your actual code and give details about anything else we need to know. Eg presumably "WorksheetName1" is a worksheet level name on the active sheet, what does it refer to and what do the referenced cells contain, Names I guess What line gives the Value error Regards, Peter T "caroline" wrote in message ... 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 . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Getting data from a closed wbook | Excel Programming | |||
Getting data from a closed wbook | 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 |