Input Txt file lines to array using vba

Question

I have a txt file and I need to input it into a string array, where each line is one item in the array.

I've done a good deal with vba before but never editing files other than Word and Excel, so this is new to me.

The below is part of my sub (copied from somewhere online so I don't really understand it)

Sub TxtFileToArray(FilePath As String, LineArray As Variant, Optional Delimiter As String = vbCrLf)
'adapted from https://www.thespreadsheetguru.com/blog/vba-guide-text-files
Dim TextFile As Integer
Dim FileContent As String
    
'Open the text file in a Read State
  TextFile = FreeFile
  Open FilePath For Input As TextFile
  
'Store file content inside a variable
  FileContent = Input(LOF(TextFile), TextFile)

'Close Text File
  Close TextFile

It fails on the line FileContent = Input(LOF(TextFile), TextFile). Error message is:

Run-time error '62':
Input past end of file

The Variable Textfile = 1, and LOF(Textfile) = 4480

What should I do?

EDIT:
The File is full of xml data (it's actually an .odc file that's been converted to .txt). Is there something I should be doing to convert it all that to a string? Perhaps I could import it as a huge string somehow and then split it into the array?

Have you double checked your txt file that you don't have any "strange" values such as space, misplaced delimiter etc? as the error indicates that the code has reach the end of the file but still looking for input values. — Wizhi, Dec 18 '20 at 10:54
The file is probably binary not text, try `Open FilePath For Binary Access Read As TextFile` - if it works then thats the case. — Alex K., Dec 18 '20 at 10:54
The file is full of xml data - is that a problem? Does that mean it won't open as a string? — Spencer Barnes, Dec 18 '20 at 10:56
@AlexK. you're right, it worked!! Feel free to submit that as an answer rather than a comment so I can accept it properly — Spencer Barnes, Dec 18 '20 at 11:05

score 1 · Answer 1 · answered Dec 18 '20 at 12:14

Text File to Array

This is just an addition to a possibly upcoming answer, to show how you can use a function for your task (I don't know exactly what binary or a binary file is).
In my short investigation, it was tested with a json file. Interesting to me is that it works with Input and Binary, and that it needs vbLf instead of vbCrLf as the Delimiter.
Note that you might get one value in the array if you choose the wrong delimiter, like it happened in this case.
The test procedure will write the lines (the values in the array) to the cells in column A of the ActiveSheet.

The Code

Option Explicit

Sub TESTtextFileToArray()
    Const FilePath As String = "F:\Test\2020\TXT\test.json"
    Dim TextLines As Variant
    ' Note the 'vbLf' instead of 'vbCrLf'.
    TextLines = TextFileToArray(FilePath, vbLf)
    If Not IsEmpty(TextLines) Then
        ' Note that 'Transpose' has a 65536 limit per dimension.
        Range("A1").Resize(UBound(TextLines) + 1).Value _
            = Application.Transpose(TextLines)
        'Debug.Print Join(TextLines, vbLf)
        MsgBox "Found " & UBound(TextLines) + 1 & " lines."
    Else
        MsgBox "No lines found."
    End If
End Sub

' The result is a 0-based 1D array.
Function TextFileToArray( _
    ByVal FilePath As String, _
    Optional Delimiter As String = vbCrLf) _
As Variant
    
    Const ProcName As String = "TextFileToArray"
    On Error GoTo clearError

    Dim TextFile As Long
    TextFile = FreeFile
    
    Open FilePath For Input Access Read As TextFile
    On Error Resume Next
    TextFileToArray = Split(Input(LOF(TextFile), TextFile), Delimiter)
    On Error GoTo clearError
    Close TextFile

ProcExit:
    Exit Function

clearError:
    Debug.Print "'" & ProcName & "': Unexpected Error!" & vbLf _
              & "    " & "Run-time error '" & Err.Number & "':" & vbLf _
              & "        " & Err.Description
    Resume ProcExit

End Function

score 0 · Answer 2 · answered Dec 18 '20 at 13:37

The easiest way is to use a Scripting.Dictionary and the FileSystemObject.

Public Function GetAsStrings(ByVal ipPath As String) As Variant

    Dim myFso  As Scripting.FileSystemObject
    Set myFso = New Scripting.FileSystemObject

    Dim myfile As TextStream
    Set myfile = myFso.OpenTextFile(ipPath, Scripting.IOMode.ForReading)
        
    Dim myStrings  As Scripting.Dictionary
    Set myStrings = New Scripting.DIctionary
    
    Do Until myfile.AtEndOfStream

        myStrings.Add mystrings.count, myfile.ReadLine
 
    Loop 
        
    myfile.Close
    Set GetAsStrings = myStrings.Items
   
End Function

Input Txt file lines to array using vba

2 Answers2

Text File to Array