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

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" <(E-Mail Removed)> wrote in message
news:150B96EE-753F-44CA-AA11-(E-Mail Removed)...
>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

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" <(E-Mail Removed)> wrote in message
> news:150B96EE-753F-44CA-AA11-(E-Mail Removed)...
> >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
>
>
>

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

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 ((E-Mail Removed), 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.

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

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 ((E-Mail Removed), 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.
>
>