Excel Interop - how to change named range

Question

I have a template excel file that I populate with data using the SQL Server OLEDB connector. The file contains some pivot tables that reference the dataset populated by the database.

I am currently doing that I select all rows in a sheet using the range "Data! $ A: $ K". This causes a problem with the null values ​​displayed in the pivot table.

What I wanted to do was create a named dataset table and assign pivot tables to it (plus I get some other benefits that the name tables bring).

The number of rows is naturally not set, so I wanted to find a way to set the scope with the named range to only valid values.

I am using Excel Interop and C # for this, and I cannot find a way to change the range. I just got to:

oRng = oSheet.get_Range("Transactions"); 

Which chooses a named range. But how do I change which cells belong to him?

Or maybe the best solution I should pursue?

EDIT

Dynamic ranges are the answer!

I solved this thanks to @TimWilliams answer:

"Use dynamic range in your template: http://ozgrid.com/Excel/DynamicRanges.htm "

I feel that dynamic ranges are being described here: http://www.contextures.com/xlpivot01.html

I had a small problem, because of which I could not use the range in the pivot table, because I required it to have at least 2 rows to work - the template file has only column headers. I added a random row to the 1st cell of the 2nd row and the pivot table adopted for this.

Subsequently, I had to delete this line using C # code.

Thanks guys for the help.

Answer 1

By doing the following, you will create a named range (transaction) on oSheet that looks at cell A1 and ends in cell C3

 Range namedRange= oSheet.Range[oSheet.Cells[1, 1], oSheet.Cells[3, 3]]; namedRange.Name = "Transactions"; 

If the name already exists in the workbook, it will be replaced if it is not created.

Answer 2

It is probably too late for WilliF, but I ran into the same problem and it worked:

 oRng = oSheet.get_Range("Transactions"); Range namedRange = oSheet.Range[oSheet.Cells[1, 1], oSheet.Cells[3, 3]]; namedRange.Name = "Transactions"; 

From here

Answer 3

First you will need to identify the occupied range of cells on the sheet. Then give him a name. You can use the UsedRange property of this leaflet, and there are several other methods.

Programmatically getting the last filled excel string using C #

How to get the range of occupied cells in excel sheet

Answer 4

You can get a named range:

 var oRng = oSheet.get_Range("Transactions"); 

You can then move or resize it using Range.get_Offset(*RowOffset*, *ColumnOffset*) or Range.get_Resize(*RowSize*, *ColumnSize*) . You can also use Offset[] and Resize[] :

 var newRange = oRng.Offset[-1,3].Resize[2,4]; 

Then you can save it as a named range as follows:

 oSheet.Names.Add("Transactions", newRange); 

PS: newRange.Name = "Transactions" does not work for me.