0

I have excel ad-in project and i'm fetching values from excel file like below by giving index of sheet row.

rowValue.Columns.Value2[1, 1];
rowValue.Columns.Value2[1, 2];

Instead of giving sheet row numbers how can I get values based on Row Header Text?

enter image description here

Neo
  • 15,491
  • 59
  • 215
  • 405
  • maybe use [ADO and an SQL query?](http://stackoverflow.com/questions/18637376/query-my-excel-worksheet-with-vba/18640226#18640226) –  Sep 22 '14 at 07:58
  • `rowValue.Columns.Value2[aCell.Row, aCell.Column];` Where `aCell` is the cell which contains the string that you want to search. Adapt [THIS](http://www.siddharthrout.com/2011/07/14/find-and-findnext-in-excel-vba/) code for C# – Siddharth Rout Sep 22 '14 at 09:32

1 Answers1

1

I actually make my own VBA function called GetColumn. It simply iterates through each column in row 1 until it reaches a cell with the text of rsHeading. Just pass in the name of the heading you are looking for, and the function will return the column number of the heading.

Public Function GetColumn(ByRef rsHeading As String) As Long
    Dim lResult As Long, i As Long
    lResult = 0
    For i = 1 To 255
        If Me.Cells(1, i) = "" Then  'end of headings reached'
            lResult = i
        ElseIf InStr(Me.Cells(1, i), rsHeading) > 0 Then  'as long as the heading contains the text; it doesn't have to exactly equal that text'
            lResult = i
        End If
        If lResult <> 0 Then
            Exit For
        End If
    Next i
    GetColumn = lResult
End Function

In your example, I would then call the function like so:

rowValue.Columns.Value2[1, GetColumn("Attribute")];
rowValue.Columns.Value2[1, GetColumn("IsMandatory")];
Bobort
  • 3,085
  • 32
  • 43
  • thanks but its VBA function i'm working in excel adin project its c# code there muust be chnage in syntax but is there anything chnaged aprt from syntax like to take out excel header text value ? – Neo Sep 23 '14 at 04:57