Results 1 to 7 of 7
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Next Line in an Array (XP)

    How does one obtain the next line or row of data in an array using vba? I can display the array in a message box and the results are listed below.

    Thanks,
    John

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Next Line in an Array (XP)

    What do you mean by "next"? And how do you display the array in the message box?

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Next Line in an Array (XP)

    Hans,

    This is the code I am using.

    Sub GetFile2()
    Dim strLineOfText As String
    Dim varFileToOpen As Variant
    Dim arrParts() As String
    Dim strUnitNew As String
    Dim strUnitPrior As String

    varFileToOpen = Application.GetOpenFilename("Text Files (*.txt), *.txt")
    If varFileToOpen = False Then Exit Sub

    Open varFileToOpen For Input As #1
    Do While Not EOF(1)
    Line Input #1, strLineOfText
    arrParts = Split(strLineOfText, ",")
    strUnitNew = arrParts(0)
    MsgBox strUnitNew
    If strUnitNew <> strUnitPrior Then
    ' some code
    Else
    ' other code
    End If
    strUnitPrior = strUnitNew
    Loop
    Close #1
    End Sub

    Regards,
    John

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Next Line in an Array (XP)

    And what exactly would you like to know? I still don't understand your original question.

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Next Line in an Array (XP)

    Hans,

    I would like to return the first, second, third and so forth of each line/row of the array. The end result would be a list of each line/row in Excel of the array.

    Regards,
    John

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Next Line in an Array (XP)

    It's rather confusing - originally you asked how to get the next line, now how to return the ??? of each line.

    arrParts(0) is the first part of the array (i.e. the line split at each comma). Similarly, arrParts(1) is the second part etc. If you want to get each part:

    Dim i As Integer
    For i = 0 To UBound(arrParts)
    ' do something with arrParts(i) here
    ...
    Next i

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Next Line in an Array (XP)

    BTW from your screenshot I get the impression that the delimiter in the text file may be a space instead of a comma.

    And why don't you use the OpenText method to open the text file directly in Excel?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •