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