Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Usedrange
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Usedrange
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Usedrange
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 | | |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Usedrange
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 "Terry VanDuzee" wrote in message ... Yes, it is a consistent block of data, only the range of cells used will vary. thank you Terry V "R. Choate" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Usedrange
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" wrote in message ... 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 "Terry VanDuzee" wrote in message ... Yes, it is a consistent block of data, only the range of cells used will vary. thank you Terry V "R. Choate" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Usedrange
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 | | |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Usedrange
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 "Terry VanDuzee" wrote in message ... 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" wrote in message ... 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 "Terry VanDuzee" wrote in message ... Yes, it is a consistent block of data, only the range of cells used will vary. thank you Terry V "R. Choate" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hide Column If Usedrange of Column ISBLANK Follow-up | Excel Discussion (Misc queries) | |||
Hide Column If Usedrange of Column ISBLANK | Excel Discussion (Misc queries) | |||
Why is worksheet.usedrange empty? | Excel Worksheet Functions | |||
Excel 2007 BUG UsedRange/LastCell differences with Excel2003. | Excel Discussion (Misc queries) | |||
Real Value of .UsedRange.Rows.Count | Excel Worksheet Functions |