I wrote up this quick class module a while back to more easily and quickly replace file Input and Inputline functions in code with an array of lines for a file. Basically, I was pre-processing and staging text files into a usable format into a database and wanted to update pre-existing code that had used the same format for input line by line using the VBA file functions.

In the example at the end of the module, I am using a custom function to read an entire CSV file split by vbCrLf into an array.

Let me know if you’re interested in getting that function too.

'Class Module InputSimulatorForArray
Option Compare Database
Option Explicit

Private arrSource As Variant
Private arrElements As Variant
Private LinePos As Long
Private ElePos As Long

Public Sub OpenInput(ArrayToIterate As Variant)
    arrSource = ArrayToIterate
    If Not EOF Then arrElements = Split(arrSource(0), ",")
    LinePos = 0
    ElePos = 0
End Sub

Public Sub InputEle(ParamArray Elements() As Variant)
    If EOF() Then GoTo Exit_Sub
    Dim it As Long
    Do While it <= UBound(Elements) And Not EOF
        Elements(it) = CStr(arrElements(ElePos))
        MoveNextEle
        it = it + 1
    Loop
    If it <= UBound(Elements) And EOF Then Elements(it) = ""
Exit_Sub:
End Sub

Public Sub InputLine(ByRef LineItm As Variant)
    If EOF() Then GoTo Exit_Sub
    Dim CurLine As Long, EleItm As String
    CurLine = LinePos
    LineItm = ""
    Do While LinePos = CurLine
        InputEle EleItm
        LineItm = LineItm & EleItm & ","
    Loop
    If Len(LineItm) > 0 Then LineItm = Left(LineItm, Len(LineItm) - 1)
Exit_Sub:
End Sub

Private Sub MoveNextEle()
    If ElePos = UBound(arrElements) Then
        MoveNextLine
    Else
        ElePos = ElePos + 1
    End If
End Sub

Private Sub MoveNextLine()
    LinePos = LinePos + 1
    If EOF() Then GoTo Exit_Sub
    arrElements = Split(arrSource(LinePos), ",")
    ElePos = 0
Exit_Sub:
End Sub

Public Function EOF() As Boolean
    EOF = LinePos > UBound(arrSource)
End Function

'Usage (this was designed to mimic the Input and InputLine functions in VBA):
' Dim is As New InputSimulatorForArray, tmp As Variant, tmp2 As Variant, tmp3 As Variant
' is.OpenInput ReadCsvFileIntoArray("Mycsvfile.csv")
' Do While Not is.EOF()
'   is.InputLine tmp
'   Debug.Print tmp
' Loop
'For individual elements you can do this:
' is.OpenInput ReadCsvFileIntoArray("Mycsvfile.csv")
' Do While Not is.EOF()
'   is.InputEle tmp, tmp2, tmp3
'   Debug.Print tmp, tmp2, tmp3
' Loop