Utility – Column to Index
Converts an Excel column letter (e.g., “A”, “AB”) to its corresponding 1-based column index.
This function creates a temporary hidden Excel.Application instance to determine the column index using the Columns(ColumnAddress).Column property. The Excel instance is properly released after use.
VB
Public Function xlColumn2Index(ColumnAddress As String) As Integer
Dim App As New Excel.Application
App.Visible = False
Dim wbBook = App.Workbooks.Add
For Each wsSheet As Worksheet In wbBook.Worksheets
Try
Dim iColumn As Integer = wsSheet.Columns(ColumnAddress).Column
wbBook.Close(SaveChanges:=False)
ReleaseObject(App)
Return iColumn
Catch ex As Exception
wbBook.Close(SaveChanges:=False)
ReleaseObject(App)
Return 0
End Try
Next
wbBook.Close(SaveChanges:=False)
ReleaseObject(App)
Return 0
End Function