VB Text to Array

Question

Hi I have a text file that I would like to assign to an array and then assign each item in the array to a custom defined variable. When I open the file in notepad, it seems as if the data is on one line and there's about 10 tabs worth of space until the next piece of information.

I use the following code to successfully view the information in a msgbox as MyArray(i).

In my code example, all the information is listed in MyArray(0) and MyArray(1) gives me an error of subscript out of range. The information in the text file seems to appear as if it were delimited by vbCrLf but that does not work either...

Is there a way to trim the spaces from MyArray(0) and then re-assign the individual data to a new array? Here's what the first two pieces of information look like from my file:

967042
144890

Public Function ReadTextFile()
  Dim TextFileData As String, myArray() As String, i As Long
  Dim strCustomVariable1 As String
  Dim strCustomVariable2 As String

  '~~> Open file as binary
  Open "C:\textfile\DATA-SND" For Binary As #1

  '~~> Read entire file's data in one go
  TextFileData = Space$(LOF(1))
  Get #1, , TextFileData

  '~~> Close File
  Close #1

  '~~> Split the data in seperate lines
  myArray() = Split(TextFileData, vbCrLf)

  For i = 0 To UBound(myArray())
      MsgBox myArray(i)
  Next
End Function

Mike Weir · Accepted Answer · 2013-08-20 13:33:15Z

1

Under normal circumstances, I'd suggest that you use Line Input instead:

Open "C:\textfile\DATA-SND" For Input As #1
    Do Until EOF(1)
        Redim Preserve myArray(i)
        Line Input #1, myArray(i)
        i = i + 1&
    Loop
Close #1

However, you're likely dealing with different end-line characters. You can use your existing code and just change it to use vbCr or vbLf instead of vbCrLf. My method assumes that your end-line characters are vbCrLf.

So for UNIX files:

myArray() = Split(TextFileData, vbLf)

And for old Mac files:

myArray() = Split(TextFileData, vbCr)

edited Aug 20, 2013 at 13:33

answered Aug 15, 2013 at 19:04

Mike Weir

3,2091 gold badge34 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Bob77 Over a year ago

Actually the text input statements such as Line Input# are perfectly happy to treat either vbCr or vbCrLf as line delimiters. It's vbLf alone that is a problem.

Bob77 Over a year ago

Mac and *nix use LFs, Old Mac used CRs.

Collectives™ on Stack Overflow

VB Text to Array

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related