CODEWITHSUNDEEP
excelvbainputtexttxt
Spencer Barnes
Spencer Barnes

Reputation: 2877

Input Txt file lines to array using vba

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?

Upvotes: 1

Views: 1508

Answers (2)

freeflow
freeflow

Reputation: 4355

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

Upvotes: 0

VBasic2008
VBasic2008

Reputation: 54817

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

Upvotes: 1

Related Questions