Usedrange

  • Thread starter Thread starter Terry VanDuzee
  • Start date Start date
T

Terry VanDuzee

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

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
(e-mail address removed)

| 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
|
|
 
Well, when I do stuff like that, I would go to the workbook I'm interested
in and select the current region, then I name the range. After that, I would
pass the range name to the macro as you describe. Does that sound like it
would work for you?
--
RMC,CPA


Yes, it is a consistent block of data, only the range of cells used will
vary.

thank you
Terry V
 
It sounds like it could work, except (and I know I did not describe this
earlier), I have 29 sheets with different ranges used, and I need to be able
to pass a name or range to a sub that would be able to use the range as a
variable value.
Finding the actual used range can be placed into a sub and only the
worksheetname has to be fed into the sub (which Im doing using
Worksheet(name).index +1. I know I'll run into problems down the road when
it comes to my chartsheets but Ill work that out then).

Thank you so much
Terry V


R. Choate said:
Well, when I do stuff like that, I would go to the workbook I'm interested
in and select the current region, then I name the range. After that, I would
pass the range name to the macro as you describe. Does that sound like it
would work for you?
--
RMC,CPA


Yes, it is a consistent block of data, only the range of cells used will
vary.

thank you
Terry V
R. Choate said:
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

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
Click to expand...
Click to expand...
 
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
 
Sounds like you have some help coming from William including code. Instead
of doing 5 things at once, lets wait and see if you've got the answer you
want from William. I didn't read his code so I cannot comment on it. I know
that I generally try to always refer to range names in my code because they
are flexible and easy to use. They are also easy to find and reset.
--
RMC,CPA


It sounds like it could work, except (and I know I did not describe this
earlier), I have 29 sheets with different ranges used, and I need to be able
to pass a name or range to a sub that would be able to use the range as a
variable value.
Finding the actual used range can be placed into a sub and only the
worksheetname has to be fed into the sub (which Im doing using
Worksheet(name).index +1. I know I'll run into problems down the road when
it comes to my chartsheets but Ill work that out then).

Thank you so much
Terry V


R. Choate said:
Well, when I do stuff like that, I would go to the workbook I'm interested
in and select the current region, then I name the range. After that, I would
pass the range name to the macro as you describe. Does that sound like it
would work for you?
--
RMC,CPA


Yes, it is a consistent block of data, only the range of cells used will
vary.

thank you
Terry V
R. Choate said:
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

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
Click to expand...
Click to expand...
 
Back
Top