Excel Interop: reference by number, not by string

Question

Good morning.

After having read the answer on a question about optimizing Excel Interop, I found that referencing a cell using worksheet.Range["A1:C3"] (same as worksheet.get_range("A1:C3")) isn't very handy. I'd like to reference the cell somehow using integer/long numbers, and I wouldn't want to map column numbers {1, 2, 3, ...} to column letters {"A", "B", "C", ...}.

Note: I know about .Cells, but this isn't an option as this only returns single cells AFAIK.

Any idea?

regards

Unless you code the logic for such a layer yourself I don't think there is a straighforward way to do this, as the excel interop is just a wrapping around the native Excel API, which works with the unhandy range syntax. Excel was tightly designed with the visual GUI and was not designed at the first place for such code abstraction. However, I welcome any solution you might find. — Mehdi LAMRANI, May 23 '11 at 09:30

score 3 · Answer 1 · answered May 23 '11 at 09:34

In Excel macro (VBA) you can use this way:

Dim rngStart As Range
Set rngStart = Range("A1")

Dim rngEnd As Range
Set rngEnd = rngStart.Rows(3).Columns(4)

Dim rngFinal As Range
Set rngFinal = Range(rngStart, rngEnd)

rngFinal.Select

It should be easy rewrite it to C#/VB.NET.

score 2 · Accepted Answer · edited Sep 01 '15 at 00:31

2

You can use the Cells property to create a Range object that can be used as argument to the Range property.

Check out the example here: http://msdn.microsoft.com/en-us/library/bb178282.aspx In the middle of the page you have an example where you use the Cells property to get two range objects that you pass to the Range property instead of passing strings:

With Worksheets(1)
    .Range(.Cells(1, 1), _
        .Cells(10, 10)).Borders.LineStyle = xlThick
End With

In general, the Cells property returns a Range object that you can do whatever you want with: http://msdn.microsoft.com/en-us/library/bb148836.aspx

edited Sep 01 '15 at 00:31

ZygD

22,092
39
79
102

answered May 23 '11 at 09:29

Rune Grimstad

35,612
10
61
76

Okay. Not really elegant but I think you're right: guess it's the "best" way of all the bad possibilities to do it. Thanks! – Atmocreations May 28 '11 at 20:18

Excel Interop: reference by number, not by string

2 Answers2