MS Excel: Get Column Letter By Index/Index By Letter (2003/2007)

While i was searching for a faster way to export datatable to Excel file, i have found this article ([^]). Everything was fine, except the getting column letters part. Then i decided to write a small code to achieve this.

Small demo is here ([^])

First, you need to set MAX_COLUMNS variable:

Excel 2003

MAX_COLUMNS = 256

Excel 2007

MAX_COLUMNS = 16384 

Then create an XML file

Dim base As String = "A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z"
Dim baseArray() As String = base.Split(",")

'temp string to hold column letter
Dim letter As String = vbNullString

'temp letter has:
'1 letter at level 1
'2 letters at level 2
'3 letters at level 3
Dim level As Integer = 1
Dim total As Long = 0
Dim i, j, k As Long

Dim xsettings As New XmlWriterSettings
xsettings.Encoding = Encoding.UTF8
xsettings.Indent = True
xsettings.NewLineOnAttributes = False
xsettings.IndentChars = vbTab

Try
    Dim xw As XmlWriter = XmlWriter.Create([file path], xsettings)
    xw.WriteStartDocument()

    xw.WriteStartElement("columns")
    xw.WriteAttributeString("version", lstExcelVersion.Text)

    'set column letter based on level
    Do
        Select Case level
            Case 1
                For i = 0 To UBound(baseArray)
                    letter = String.Format("{0}", baseArray(i))

                    xw.WriteStartElement("column")
                    xw.WriteAttributeString("index", total + 1)
                    xw.WriteAttributeString("letter", letter)
                    xw.WriteEndElement()

                    total += 1
                Next

            Case 2
                For i = 0 To UBound(baseArray)
                    For j = 0 To UBound(baseArray)
                        letter = String.Format("{0}{1}",           baseArray(i), baseArray(j))

                        xw.WriteStartElement("column")
                        xw.WriteAttributeString("index", total + 1)
                        xw.WriteAttributeString("letter", letter)
                        xw.WriteEndElement()

                        total += 1

                        If lstExcelVersion.SelectedIndex = 0 And total = MAX_COLUMNS Then Exit Do
                    Next
                Next

            Case 3
                For i = 0 To UBound(baseArray)
                    For j = 0 To UBound(baseArray)
                        For k = 0 To UBound(baseArray)
                            letter = String.Format("{0}{1}{2}", baseArray(i), baseArray(j), baseArray(k))

                            xw.WriteStartElement("column")
                            xw.WriteAttributeString("index", total + 1)
                            xw.WriteAttributeString("letter", letter)
                            xw.WriteEndElement()

                            total += 1

                            If lstExcelVersion.SelectedIndex = 1 And total = MAX_COLUMNS Then Exit Do
                        Next
                    Next
                Next

        End Select

        level += 1
    Loop Until level = 4

    xw.WriteEndElement()
    xw.WriteEndDocument()
    xw.Flush()
    xw.Close()

    MsgBox("Creation of xml file completed")

Catch ex As Exception
    Throw ex
End Try

Then get column letter by index/index by column letter

Private Function GetColumn(ByVal by As String, ByVal value As String) As String
        Dim returnType As String = vbNullString
        Dim returnValue As String = vbNullString
        Dim query As String = String.Format("column[@{0}='{1}']", by, value)
        Select Case by
            Case "index" : returnType = "letter"
            Case "letter" : returnType = "index"
        End Select
        Dim xdoc As New XmlDocument()
        xdoc.Load([file path])
        'select root of the document
        Dim root As XmlElement = xdoc.DocumentElement
        'select the column
        Dim xn As Xml.XmlNode = root.SelectSingleNode(query)
        If Not (xn Is Nothing) Then
            returnValue = xn.Attributes(returnType).InnerText
        End If
        root = Nothing
        xdoc = Nothing
        Return returnValue
End Function