Excel VBA

css navigation by Css3Menu.com

Determine Parse Position

I was struggling to count positions of items to be parsed from a long report. This simply opens a text (flat) file and counts each character on the line.
Sub DeterminePosition()
'Used to create a file to determine position across lines
    Dim AlphaArray, daNumber, dLog
    Close #1, #2
    AlphaArray = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 0, _
       1, 2, 3, 4, 5, 6, 7, 8, 9, 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, _
       0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, _
       0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 0, _
       1, 2, 3, 4, 5, 6, 7, 8, 9, 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 0, 1, _
       2, 3, 4, 5, 6, 7, 8, 9, 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 0, 1, 2, _
       3, 4, 5, 6, 7, 8, 9, 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 0, 1, 2, 3, _
       4, 5, 6, 7, 8, 9, 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 0, 1, 2, 3, 4, _
       5, 6, 7, 8, 9, 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 0)
    Open "c:\documents and settings\" & Application.UserName & _
        "\my documents\BENT0404" For Input As #2
    Open "c:\documents and settings\" & Application.UserName & _
        "\my documents\Count0404B.txt" For Output As #1
        Do While Not EOF(2)
            Line Input #2, Brick2
 'Line Input does the whole row; 
                                                ' plain Input line-feeds at commas
                Print #1, Brick2
                For X = 1 To Len(Brick2)
                    daNumber = daNumber & AlphaArray(X)
                Next
            Print #1, daNumber
            dLog = Null
            daNumber = Null
        Loop
    Close #1, #2
    MsgBox "Done", vbInformation
End Sub

Example Output:
this Application for Payment has been completed in accordance 
123456789012345678901234567890123456789012345678901234567

Having done this, I was able to produce the last report in record time because I could determine position so quickly.

© 2004-2017

Updated:  04/06/2017 20:32
This page added:  17 April 2004