Hello
How can I get the usedrange from a sheet in a closed workbook?

This is how I am getting the values:

With ActiveSheet.Range(cellrange)
.FormulaArray = "='" & fpath & "\[" & fname & "]" _
& sname & "'!" & cellrange
End With

Where cellrange right now is a variable that contains a range that is
hardcoded. I want to be able to find the usedrange in the closed workbook,
to pass it as a variable to this sub from the calling sub.


Thank you
Terry V

Would the used range be a range of contiguous populated cells, bound by a
blank row and a blank column? In other words, would this be the current
region on a sheet if you were to click on one cell in the range?
--

RMC,CPA

"Terry VanDuzee" wrote in message
...
Hello
How can I get the usedrange from a sheet in a closed workbook?

This is how I am getting the values:

With ActiveSheet.Range(cellrange)
.FormulaArray = "='" & fpath & "\[" & fname & "]" _
& sname & "'!" & cellrange
End With

Where cellrange right now is a variable that contains a range that is
hardcoded. I want to be able to find the usedrange in the closed workbook,
to pass it as a variable to this sub from the calling sub.


Thank you
Terry V

Hi Terry

Assume you wish to retrieve data into sheet "GetData" of the active workbook
from a sheet called "MySheet" in a closed workbook called "Tester.xls"
(which also has a sheet called "MyRange").

Firstly, I'd place the following code as a "Workbook Close" event in
"Tester.xls".
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("MyRange").Range("A1") = Sheets("MySheet").UsedRange.Address
End Sub


Then, to extract the data from "MySheet" into the active workbook...

Sub Retrieve()
Dim cellrange As String, fpath As String, fname As String
Dim sname2 As String, sname As String, s As String
Dim srange As Range

fpath = "C:\~MS\WORK"
fname = "Tester.xls"
sname = "MySheet"
sname2 = "MyRange"
cellrange = "A1"

With ThisWorkbook.Sheets("GetData")
'Identify the range to be used
..Range("A1").Formula = "='" & fpath & "\[" & fname & "]" & sname2 & "'!" &
cellrange
..Range("A1").Formula = .Range("A1").Value

'Import the data
Set srange = .Range(.Range("A1"))
s = .Range("A1")
srange.FormulaArray = "='" & fpath & "\[" & fname & "]" & sname & "'!" & s

'To convert formulae to values
'srange.Formula = srange.Value2
End With

End Sub



--
XL2002
Regards

William


"Terry VanDuzee" wrote in message
...
| Hello
| How can I get the usedrange from a sheet in a closed workbook?
|
| This is how I am getting the values:
|
| With ActiveSheet.Range(cellrange)
| .FormulaArray = "='" & fpath & "\[" & fname & "]" _
| & sname & "'!" & cellrange
| End With
|
| Where cellrange right now is a variable that contains a range that is
| hardcoded. I want to be able to find the usedrange in the closed
workbook,
| to pass it as a variable to this sub from the calling sub.
|
|
| Thank you
| Terry V
|
|

William
Im looking at your code and trying to figure it out. I get the gist of it
(no offence intended, Im not really good with the code yet). It may very
well work if I can manipulate it properly. I'd have to find an unused cell
on each sheet so that I can hardcode the cell address. I do know that the
worksheet data on each sheet will stay the same, I should be able to do it.
And on the open event for each sheet, I can delete the contents of that used
cell, and re-write it on the close event.

Thank you so much
Terry V
"William" wrote in message
...
Hi Terry

Assume you wish to retrieve data into sheet "GetData" of the active
workbook
from a sheet called "MySheet" in a closed workbook called "Tester.xls"
(which also has a sheet called "MyRange").

Firstly, I'd place the following code as a "Workbook Close" event in
"Tester.xls".
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("MyRange").Range("A1") = Sheets("MySheet").UsedRange.Address
End Sub


Then, to extract the data from "MySheet" into the active workbook...

Sub Retrieve()
Dim cellrange As String, fpath As String, fname As String
Dim sname2 As String, sname As String, s As String
Dim srange As Range

fpath = "C:\~MS\WORK"
fname = "Tester.xls"
sname = "MySheet"
sname2 = "MyRange"
cellrange = "A1"

With ThisWorkbook.Sheets("GetData")
'Identify the range to be used
.Range("A1").Formula = "='" & fpath & "\[" & fname & "]" & sname2 & "'!" &
cellrange
.Range("A1").Formula = .Range("A1").Value

'Import the data
Set srange = .Range(.Range("A1"))
s = .Range("A1")
srange.FormulaArray = "='" & fpath & "\[" & fname & "]" & sname & "'!" & s

'To convert formulae to values
'srange.Formula = srange.Value2
End With

End Sub



--
XL2002
Regards

William


"Terry VanDuzee" wrote in message
...
| Hello
| How can I get the usedrange from a sheet in a closed workbook?
|
| This is how I am getting the values:
|
| With ActiveSheet.Range(cellrange)
| .FormulaArray = "='" & fpath & "\[" & fname & "]" _
| & sname & "'!" & cellrange
| End With
|
| Where cellrange right now is a variable that contains a range that is
| hardcoded. I want to be able to find the usedrange in the closed
workbook,
| to pass it as a variable to this sub from the calling sub.
|
|
| Thank you
| Terry V
|
|