Using column names in spreadsheet no index, using vb,net

Question

I have been writing some code to extract data from a an application and parse it to a spreadsheet.

My spreadsheet looks like this:

Scenario     ClientName         ClientNumber

5555        Smith               s0001

6776        Charles           d6666

I have this code:

Dim ObjExcel As New Excel.Application Dim sWindow As New WinWindow ObjExcel.Visible = False Dim stext As String

            ObjExcel.Workbooks.Open("c:\data\calcresults.xlsx")
            Dim ObjWS As Excel.Worksheet = ObjExcel.Worksheets("IP")

            Dim iNextRow As Integer = ObjWS.UsedRange.End(Excel.XlDirection.xlDown).Row + 1

            ObjWS.Cells(iNextRow,1 ) = "d66666"
            ObjWS.Cells(iNextRow, 2) = "s77898"

would like use to Column Name not index, for example:

ObjWS.Cells(iNextRow,"Scenario" ) = "new row data, first column"

any ideas how can i do this?

Have a look at Siddharth's answer here. http://stackoverflow.com/questions/10106465/excel-column-number-from-column-name — Alistair Weir, Sep 27 '12 at 14:07
Simply put, there is no way to refer to cells based on their values - so no, you can't just write `ObjWS.Cells(iNextRow,"Scenario" )` and expect this to work. You might wan't to let us in on the details of your import, because as I see it, this could be improved better all together. Otherwise - look at Weir's link ;) — Jook, Sep 27 '12 at 14:08
Thanks for your response. I am trying to extract value from a field i.e. Scenario and parse it to the column named "Scenario" - with 300 columns it would be easier to refer to the columns by name rather than index. I'd appreciate any help. — user1689661, Sep 27 '12 at 14:15

score 0 · Answer 1 · answered Oct 04 '12 at 15:04

I am guessing by your post that you are opening a workbook and updating the same column values each time?

What you could do is name the Range in Excel by selecting the cell in the sheet and entering the name into the Name Box as follows:

Excel

Then you can manipulate as follows:

Dim r1 As Range
Set r1 = ActiveSheet.Range("Scenario")

r1.Value = "OOps, changed it!"
r1.Offset(1, 0).Value = "This is A2"

Additionally you can just refer to the range:

Dim r As Range
Set r = ActiveSheet.Range("A1")
r.Value = "gello"

You can also set the name of a range for more than one cell - i.e. multiple rows/columns. Then manipulate with:

Dim r2 As Range
Set r2 = ActiveSheet.Range("SomethingElse")
r2.Cells(1) = "Summit Else 0"
r2.Cells(2) = "Summit Else 1"
r2.Cells(3) = "Summit Else 2"

I cannot see anything wrong with just accessing row/col index.

Using column names in spreadsheet no index, using vb,net

1 Answers1