Getting data from a closed wbook (adaptation of rondebruin's)

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
 
P

Peter T

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
 
C

caroline

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 said:
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 said:
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
Click to expand...


.
Click to expand...
 
P

Peter T

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 said:
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 said:
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 said:
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
Click to expand...


.
Click to expand...
Click to expand...
 
C

caroline

Thanks Peter will do. I appreciated your help.
--
caroline


Peter T said:
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 said:
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 said:
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

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


.
Click to expand...
Click to expand...


.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to hide password? 1
Find string from column A in Range("B2:F7") list the header of that column/s 5
Code does not error but no copy either 4
vArray = Sheet2.Range("A1:A" & lr) troubles ahoy 11
Transfer array element offset value to a matched value 3
Copy row info but not in normal column sequence 6
Snag in invoice generator. How to change the "series" 5
Get data reference to last row 5

Top