Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto size named range by .Net or VBA
I have a question about adding data to a named range.
I have an application that takes data from an Oracle database and writes it to a specified Excel file starting at a column and row specified in the command line. I would like to change that so it writes to a named range. Because there are several macros in the Excel template I am writing to, those macros need to select ranges sized precicely for the data existing in the sheet and not, as they currently do, by selecting huge numbers of columns and cells just to cover all possibilities. So, my question is, if I have a named range as a single cell as a starting point, how would I add data to that row, then the next row, etc., etc., in a way that would cause the named range to automatically expand as I added (inserted?) the data? Also, for a couple reports, I need other named ranges below the target range to automatically move down in the report as I insert new rows to the target named range - those other named ranges will become the target for other sets of data. Thanks in advance for any help or suggestions. Dale -- Dale Preston MCAD C# MCSE, MCDBA |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto size named range by .Net or VBA
If the named range includes an extra blank row beneath the last data row, it
will expand automatically if rows are inserted below the last data row. Another option is to use a QueryTable, which has an option to range name the resulting recordset. Hard to say which method is best suited since I don't get your scenario exactly. "Dale" wrote in message ... I have a question about adding data to a named range. I have an application that takes data from an Oracle database and writes it to a specified Excel file starting at a column and row specified in the command line. I would like to change that so it writes to a named range. Because there are several macros in the Excel template I am writing to, those macros need to select ranges sized precicely for the data existing in the sheet and not, as they currently do, by selecting huge numbers of columns and cells just to cover all possibilities. So, my question is, if I have a named range as a single cell as a starting point, how would I add data to that row, then the next row, etc., etc., in a way that would cause the named range to automatically expand as I added (inserted?) the data? Also, for a couple reports, I need other named ranges below the target range to automatically move down in the report as I insert new rows to the target named range - those other named ranges will become the target for other sets of data. Thanks in advance for any help or suggestions. Dale -- Dale Preston MCAD C# MCSE, MCDBA |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto size named range by .Net or VBA
A little more information:
The .Net code I am writing opens a .xls file template and populates the data. There are three datasets that have to go into this file. At the top of the file is some over all header/title rows. Then a row with the formatted column headers for the first dataset. Under that is a row with the formatted column headers for the second dataset. And under that the headers for the third dataset. There can easily be a couple empty rows between the header rows. The named ranges should be immediately below their related header row. When I add the data for the first dataset under its header row, I need the other header rows to move down and their related named ranges to move down with them. Thanks, Dale "-" wrote: If the named range includes an extra blank row beneath the last data row, it will expand automatically if rows are inserted below the last data row. Another option is to use a QueryTable, which has an option to range name the resulting recordset. Hard to say which method is best suited since I don't get your scenario exactly. "Dale" wrote in message ... I have a question about adding data to a named range. I have an application that takes data from an Oracle database and writes it to a specified Excel file starting at a column and row specified in the command line. I would like to change that so it writes to a named range. Because there are several macros in the Excel template I am writing to, those macros need to select ranges sized precicely for the data existing in the sheet and not, as they currently do, by selecting huge numbers of columns and cells just to cover all possibilities. So, my question is, if I have a named range as a single cell as a starting point, how would I add data to that row, then the next row, etc., etc., in a way that would cause the named range to automatically expand as I added (inserted?) the data? Also, for a couple reports, I need other named ranges below the target range to automatically move down in the report as I insert new rows to the target named range - those other named ranges will become the target for other sets of data. Thanks in advance for any help or suggestions. Dale -- Dale Preston MCAD C# MCSE, MCDBA |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto size named range by .Net or VBA
hi,
There is no way to have the named range expand as you add data that I know of but there is a way to resize the named range after you have added the data. I once had an xl file that was used as a table in an access db. so as users added data to the xl file, it had to also show up in the access db. so before the user exited the file, i had xl to resized the table(named range). This may not work for you "as is" but maybe it will give some ideas and have it resize directly after import. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) ' Macro Written 5/9/2002 by FSt1 Dim rng As Range Dim sht As Worksheet Set rng = ActiveCell 'remember this cell Set sht = ActiveSheet 'remember this sheet Sheets("WO").Select 'goto the sheet Range("A1").Select 'goto the start point Range(Range("A1"), Range("A1").Offset(0, 1) _ .End(xlDown).Offset(0, 7)).Select 'select the range to resize ActiveWorkbook.Names.Add Name:="WOrders", _ RefersToR1C1:=Selection 'Resize the range Range("A1").Select 'clear the selection sht.Select 'go back to the last sheet rng.Select 'go back to the last cell End Sub works in up to xp. not sure about 07. I haven't worked with that yet. as to your other concern, if they are real named ranges, they will move down as you add rows and move up if you delete rows. Hoped this helped. Regards FSt1 "Dale" wrote: I have a question about adding data to a named range. I have an application that takes data from an Oracle database and writes it to a specified Excel file starting at a column and row specified in the command line. I would like to change that so it writes to a named range. Because there are several macros in the Excel template I am writing to, those macros need to select ranges sized precicely for the data existing in the sheet and not, as they currently do, by selecting huge numbers of columns and cells just to cover all possibilities. So, my question is, if I have a named range as a single cell as a starting point, how would I add data to that row, then the next row, etc., etc., in a way that would cause the named range to automatically expand as I added (inserted?) the data? Also, for a couple reports, I need other named ranges below the target range to automatically move down in the report as I insert new rows to the target named range - those other named ranges will become the target for other sets of data. Thanks in advance for any help or suggestions. Dale -- Dale Preston MCAD C# MCSE, MCDBA |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto size named range by .Net or VBA
Hello Dale,
From your post, my understanding on this issue is: you want to know how to dynamically resize the Named Range when new rows are appended to it and how to adjust the named range below the table. If I'm off base, please feel free to let me know. For the first question, Named range will be adjusted automatically if the Insert (row) method is called on cells within it. Suppose we get the Range object of the last row and call its 'Insert' method, we may find that the new row is automatically added into the named range: Excel.Range lastRow = (Excel.Range)name.RefersToRange.Rows[rows.Count, missing]; lastRow.Insert(Excel.XlDirection.xlDown, true); But unfortunately, Excel does not support inserting a row below the caller (such as the 'lastRow' in the above code). The new blank row appears before the 'lastRow'. A possible workaround is to temporarily make the original named range include an extra row beneath the last data row, then call Insert on this extra row. When the datasets are imported, we remove the row from the named range. As FSt1 suggested, another method is to adjust the named range every time when a new row or cell is added by calling NamedRange.RefersToR1C1 = <new range. For the second question, the named ranges below the table will be shifted automatically by Excel when new rows are added to the table. Please let me know if you have any other concerns, or need anything else. Sincerely, Jialiang Ge , remove 'online.') Microsoft Online Community Support ================================================== For MSDN subscribers whose posts are left unanswered, please check this document: http://blogs.msdn.com/msdnts/pages/postingAlias.aspx Get notification to my posts through email? Please refer to http://msdn.microsoft.com/subscripti...ult.aspx#notif ications. If you are using Outlook Express/Windows Mail, please make sure you clear the check box "Tools/Options/Read: Get 300 headers at a time" to see your reply promptly. Note: The MSDN Managed Newsgroup support offering is for non-urgent issues where an initial response from the community or a Microsoft Support Engineer within 1 business day is acceptable. Please note that each follow up response may take approximately 2 business days as the support professional working with you may need further investigation to reach the most efficient resolution. The offering is not appropriate for situations that require urgent, real-time or phone-based interactions or complex project analysis and dump analysis issues. Issues of this nature are best handled working with a dedicated Microsoft Support Engineer by contacting Microsoft Customer Support Services (CSS) at http://msdn.microsoft.com/subscripti...t/default.aspx. ================================================== This posting is provided "AS IS" with no warranties, and confers no rights. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto size named range by .Net or VBA
Thanks FSt1.
"FSt1" wrote: hi, There is no way to have the named range expand as you add data that I know of but there is a way to resize the named range after you have added the data. I once had an xl file that was used as a table in an access db. so as users added data to the xl file, it had to also show up in the access db. so before the user exited the file, i had xl to resized the table(named range). This may not work for you "as is" but maybe it will give some ideas and have it resize directly after import. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) ' Macro Written 5/9/2002 by FSt1 Dim rng As Range Dim sht As Worksheet Set rng = ActiveCell 'remember this cell Set sht = ActiveSheet 'remember this sheet Sheets("WO").Select 'goto the sheet Range("A1").Select 'goto the start point Range(Range("A1"), Range("A1").Offset(0, 1) _ .End(xlDown).Offset(0, 7)).Select 'select the range to resize ActiveWorkbook.Names.Add Name:="WOrders", _ RefersToR1C1:=Selection 'Resize the range Range("A1").Select 'clear the selection sht.Select 'go back to the last sheet rng.Select 'go back to the last cell End Sub works in up to xp. not sure about 07. I haven't worked with that yet. as to your other concern, if they are real named ranges, they will move down as you add rows and move up if you delete rows. Hoped this helped. Regards FSt1 "Dale" wrote: I have a question about adding data to a named range. I have an application that takes data from an Oracle database and writes it to a specified Excel file starting at a column and row specified in the command line. I would like to change that so it writes to a named range. Because there are several macros in the Excel template I am writing to, those macros need to select ranges sized precicely for the data existing in the sheet and not, as they currently do, by selecting huge numbers of columns and cells just to cover all possibilities. So, my question is, if I have a named range as a single cell as a starting point, how would I add data to that row, then the next row, etc., etc., in a way that would cause the named range to automatically expand as I added (inserted?) the data? Also, for a couple reports, I need other named ranges below the target range to automatically move down in the report as I insert new rows to the target named range - those other named ranges will become the target for other sets of data. Thanks in advance for any help or suggestions. Dale -- Dale Preston MCAD C# MCSE, MCDBA |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto size named range by .Net or VBA
Thanks, Jialiang. That helps a lot with my assessment for the customer.
Dale "Jialiang Ge [MSFT]" wrote: Hello Dale, From your post, my understanding on this issue is: you want to know how to dynamically resize the Named Range when new rows are appended to it and how to adjust the named range below the table. If I'm off base, please feel free to let me know. For the first question, Named range will be adjusted automatically if the Insert (row) method is called on cells within it. Suppose we get the Range object of the last row and call its 'Insert' method, we may find that the new row is automatically added into the named range: Excel.Range lastRow = (Excel.Range)name.RefersToRange.Rows[rows.Count, missing]; lastRow.Insert(Excel.XlDirection.xlDown, true); But unfortunately, Excel does not support inserting a row below the caller (such as the 'lastRow' in the above code). The new blank row appears before the 'lastRow'. A possible workaround is to temporarily make the original named range include an extra row beneath the last data row, then call Insert on this extra row. When the datasets are imported, we remove the row from the named range. As FSt1 suggested, another method is to adjust the named range every time when a new row or cell is added by calling NamedRange.RefersToR1C1 = <new range. For the second question, the named ranges below the table will be shifted automatically by Excel when new rows are added to the table. Please let me know if you have any other concerns, or need anything else. Sincerely, Jialiang Ge , remove 'online.') Microsoft Online Community Support ================================================== For MSDN subscribers whose posts are left unanswered, please check this document: http://blogs.msdn.com/msdnts/pages/postingAlias.aspx Get notification to my posts through email? Please refer to http://msdn.microsoft.com/subscripti...ult.aspx#notif ications. If you are using Outlook Express/Windows Mail, please make sure you clear the check box "Tools/Options/Read: Get 300 headers at a time" to see your reply promptly. Note: The MSDN Managed Newsgroup support offering is for non-urgent issues where an initial response from the community or a Microsoft Support Engineer within 1 business day is acceptable. Please note that each follow up response may take approximately 2 business days as the support professional working with you may need further investigation to reach the most efficient resolution. The offering is not appropriate for situations that require urgent, real-time or phone-based interactions or complex project analysis and dump analysis issues. Issues of this nature are best handled working with a dedicated Microsoft Support Engineer by contacting Microsoft Customer Support Services (CSS) at http://msdn.microsoft.com/subscripti...t/default.aspx. ================================================== This posting is provided "AS IS" with no warranties, and confers no rights. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
auto insert worksheet name in dynamic named range | Excel Programming | |||
Auto-detect range size | Excel Programming | |||
increasing the size of a named range and saving it back to the spreadsheetI | Excel Programming | |||
Cannot Expand Named Range - when size of the Range exceeds | Excel Discussion (Misc queries) | |||
determine range size after auto filter? | Excel Programming |