Excel Interop - How to change named range

Question

I have a template excel file that I fill data into from SQL Server OLEDB connector. The file contains some pivot tables that refer to the dataset being filled in by the database.

Currently what I do is that I select all rows in sheet by using "Data!$A:$K" range. This brings a problem with blank values being shown in the pivot table.

What I wanted to do is to create a named table over the dataset and refer the pivot tables to that (plus I gain some other advantages that names tables bring).

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

I'm using Excel Interop and C# for that and I can't find a way to change the range. I only got as far as:

oRng = oSheet.get_Range("Transactions");

Which selects the named range. But how do I change which cells belong to it?

Or is there perhaps a better solution I should pursue?

EDIT

Dynamic ranges are the answer!

I solved this thanks to @TimWilliams reply:

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

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

I encountered a slight issue that I couldn't use the range in pivot table because it demanded it needs at least 2 rows to operate - the template file only has column headings. I added a random string to 1st cell of 2nd row and pivot table accepted that.

Afterwards I had to remove that row using c# code.

Thank you guys for help.

Answer 1

By doing the following, you will create a named range (Transactions) on the oSheet staring at cell A1 and finising at cell C3

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

If the name already exist in the workbook it will be replace, if not it will be created.

Answer 2

It's probably too late for WilliF but I came across the same issue and this 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 in the sheet. Then assign it a name. You can use the UsedRange property of the worksheet to obtain this, and there are a few other methods as well.

Programmatically getting the last filled excel row using C#

How to get the range of occupied cells in excel sheet