values from workbook without opening it

[caroline]

hello
I would like to use the values of a range (same range in same worksheet
name), but from different workbooks (could be up to 40).


I am currently doing it but my code involves opening each workbook which is
cumbersome.
Once the workbook are opened the following code is applied
' Test to see if the file is open. if not opened then message
If Not IsFileOpen(Range("WorkbookNameCell1").Offset(i, 0).Value)
Then
MsgBox "" & Range("WorkbookNameCell1").Offset(i, 0).Value &
" is not opened or the workbook name is misspell." & vbCr & vbCr & _
"Open it or check the spelling on your list (do not forget
the extension .xls)"
Range("WorkbookNameCell1").Offset(i, 0).Select
Exit Sub

End If

'take appropriate values
Dim i As Long
For i = 0 To 29
Dim Expt(1 To 8) As Range
Set Expt(1) = Workbooks(Range("WorkbookNameCell1").Offset(i,
0).Value).Worksheets("Forecast").Range("H8:I39")
etc

In order not to open the workbooks, and because I do not know in advance
which path the user will be using,
I would like to write the path in a cell
MyPath = Range("PathName").Value
and add the path in the code, so I do not have to open each workbook.

Any help greatly appreciated. Thanks

 

[Gary''s Student]

You can have your code do exactly what a human would do:

1. select a cell
2. insert the referencing formula\
3. capture the value

something like:

='C:\[data.xls]Sheet1'!$B$9

Your code would splice together the formula string and then

Range("Z100").Formula = the_string

do an Application.Calculate and then get the value.

 

[john]

you can get data from a closed workbook using formula.
try following as an idea:

Sub GetData()
Dim mydata As String
'data location & range to copy
mydata = "='C:\[NewBook.xls]Sheet1'!$B$2:F12" '<< change as required

'link to worksheet
With Worksheets(1).Range("B2:F12") '<< change as required
.Formula = mydata

'convert formula to text
.Copy
.PasteSpecial Paste:=xlPasteValues
End With
Application.CutCopyMode = False
End Sub

 

[caroline]

sorry to be thick but I do not understand what I ahve to do
I wrote the path including the file, sheet and range name in A1
=C:\Documents and Settings\Administrator.CHANGEME\My
Documents\file4\[template.xls]sheet1!H8:I39
then
If A1 is named PathName
I wrote in VBA
Dim MyPath As String
MyPath = Range("PathName").Value
Range("H8:I39).value=Mypath.value
And it did not work.
I thank you in advance for your help

--
caroline

You can have your code do exactly what a human would do:

1. select a cell
2. insert the referencing formula\
3. capture the value

something like:

='C:\[data.xls]Sheet1'!$B$9

Your code would splice together the formula string and then

Range("Z100").Formula = the_string

do an Application.Calculate and then get the value.
--
Gary''s Student - gsnu200903

hello
I would like to use the values of a range (same range in same worksheet
name), but from different workbooks (could be up to 40).


I am currently doing it but my code involves opening each workbook which is
cumbersome.
Once the workbook are opened the following code is applied
' Test to see if the file is open. if not opened then message
If Not IsFileOpen(Range("WorkbookNameCell1").Offset(i, 0).Value)
Then
MsgBox "" & Range("WorkbookNameCell1").Offset(i, 0).Value &
" is not opened or the workbook name is misspell." & vbCr & vbCr & _
"Open it or check the spelling on your list (do not forget
the extension .xls)"
Range("WorkbookNameCell1").Offset(i, 0).Select
Exit Sub

End If

'take appropriate values
Dim i As Long
For i = 0 To 29
Dim Expt(1 To 8) As Range
Set Expt(1) = Workbooks(Range("WorkbookNameCell1").Offset(i,
0).Value).Worksheets("Forecast").Range("H8:I39")
etc

In order not to open the workbooks, and because I do not know in advance
which path the user will be using,
I would like to write the path in a cell
MyPath = Range("PathName").Value
and add the path in the code, so I do not have to open each workbook.

Any help greatly appreciated. Thanks

 

[caroline]

Sorry I am still struggling :
I do not want to write the path, name of workbook,
C:\[NewBook.xls]Sheet1'!$B$2:F12 in the code (as I want the user to enter
this in a cell and this use it in the code.
thanks
--
caroline

you can get data from a closed workbook using formula.
try following as an idea:

Sub GetData()
Dim mydata As String
'data location & range to copy
mydata = "='C:\[NewBook.xls]Sheet1'!$B$2:F12" '<< change as required

'link to worksheet
With Worksheets(1).Range("B2:F12") '<< change as required
.Formula = mydata

'convert formula to text
.Copy
.PasteSpecial Paste:=xlPasteValues
End With
Application.CutCopyMode = False
End Sub


--
jb

hello
I would like to use the values of a range (same range in same worksheet
name), but from different workbooks (could be up to 40).


I am currently doing it but my code involves opening each workbook which is
cumbersome.
Once the workbook are opened the following code is applied
' Test to see if the file is open. if not opened then message
If Not IsFileOpen(Range("WorkbookNameCell1").Offset(i, 0).Value)
Then
MsgBox "" & Range("WorkbookNameCell1").Offset(i, 0).Value &
" is not opened or the workbook name is misspell." & vbCr & vbCr & _
"Open it or check the spelling on your list (do not forget
the extension .xls)"
Range("WorkbookNameCell1").Offset(i, 0).Select
Exit Sub

End If

'take appropriate values
Dim i As Long
For i = 0 To 29
Dim Expt(1 To 8) As Range
Set Expt(1) = Workbooks(Range("WorkbookNameCell1").Offset(i,
0).Value).Worksheets("Forecast").Range("H8:I39")
etc

In order not to open the workbooks, and because I do not know in advance
which path the user will be using,
I would like to write the path in a cell
MyPath = Range("PathName").Value
and add the path in the code, so I do not have to open each workbook.

Any help greatly appreciated. Thanks

 

[caroline]

Got it!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
I needed to write in the cell
'='C:\[NewBook.xls]Sheet1'!$B$2:F12
then it all makes sense
Thanks to John and gsnu200903
--
caroline

Sorry I am still struggling :
I do not want to write the path, name of workbook,
C:\[NewBook.xls]Sheet1'!$B$2:F12 in the code (as I want the user to enter
this in a cell and this use it in the code.
thanks
--
caroline

you can get data from a closed workbook using formula.
try following as an idea:

Sub GetData()
Dim mydata As String
'data location & range to copy
mydata = "='C:\[NewBook.xls]Sheet1'!$B$2:F12" '<< change as required

'link to worksheet
With Worksheets(1).Range("B2:F12") '<< change as required
.Formula = mydata

'convert formula to text
.Copy
.PasteSpecial Paste:=xlPasteValues
End With
Application.CutCopyMode = False
End Sub


--
jb

hello
I would like to use the values of a range (same range in same worksheet
name), but from different workbooks (could be up to 40).


I am currently doing it but my code involves opening each workbook which is
cumbersome.
Once the workbook are opened the following code is applied
' Test to see if the file is open. if not opened then message
If Not IsFileOpen(Range("WorkbookNameCell1").Offset(i, 0).Value)
Then
MsgBox "" & Range("WorkbookNameCell1").Offset(i, 0).Value &
" is not opened or the workbook name is misspell." & vbCr & vbCr & _
"Open it or check the spelling on your list (do not forget
the extension .xls)"
Range("WorkbookNameCell1").Offset(i, 0).Select
Exit Sub

End If

'take appropriate values
Dim i As Long
For i = 0 To 29
Dim Expt(1 To 8) As Range
Set Expt(1) = Workbooks(Range("WorkbookNameCell1").Offset(i,
0).Value).Worksheets("Forecast").Range("H8:I39")
etc

In order not to open the workbooks, and because I do not know in advance
which path the user will be using,
I would like to write the path in a cell
MyPath = Range("PathName").Value
and add the path in the code, so I do not have to open each workbook.

Any help greatly appreciated. Thanks