batch update excel column line no.

 操作excel的时候，为了程序的可扩展性，在程序中自定义excel各列对应的索引号，如果在后期，需要向excel中添加新列，那么得更改新列后面的所有的列索引号。

手工更改是蛮枯燥，而且很容易出错。所以在开发环境中，自定义了一个宏，达到批量更改的目的。

Sub UpdateColumnLineNo()

        Dim ts As TextSelection = DTE.ActiveDocument.Selection()
        Dim commentString As String, searchString As String, errorNo As String
        Dim index As Integer, endlineIndex As Integer
        Dim lineText As String, lineComment As String
        Dim count As Integer = 1
        Dim endlineflag As Char, equalflag As Char

        Dim tsSave As TextPoint = ts.ActivePoint.CreateEditPoint()
        ts.StartOfDocument()

        searchString = "Column_"
        commentString = "--"

        endlineflag = ";"
        equalflag = "="

        Do While (ts.FindText("*" + searchString + "*", 1024))

            '   remove spaces from end of line
            lineText = RTrim(ts.Text())

            index = lineText.IndexOf(equalflag)
            endlineIndex = lineText.IndexOf(endlineflag)

            lineComment = lineText.Substring(endlineIndex, lineText.Length - endlineIndex)
            ts.Text = String.Format("{0} {1}{2}", lineText.Substring(0, index + 1), count, lineComment)
            count += 1
        Loop
        ts.MoveToPoint(tsSave)
    End Sub