Option Explicit

Private m_filepath As String
Private m_delimiter As String
Private m_numberOfLines As Long
Private m_numberOfColumns As Long
Private m_data() As String
Public Sub initalize(filepath As String, delimiter As String)
    m_filepath = filepath
    m_delimiter = delimiter
End Sub
Public Sub read(filepath As String, delimiter As String)
    Call initalize(filepath, delimiter)
    Call readData
End Sub
Private Sub readData()
    Dim sDelimiter As String * 1
    Dim SingleLineString As String
    Dim ParsedSingleLineString() As String
    Dim ParsedLineStrings() As String
    Dim r As Long
    Dim fn As Long
    Dim NewText As String

    On Error GoTo ErrorHandle

    'sSourceFile doesn't exist
    If Len(Dir(m_filepath)) = 0 Then GoTo ErrorHandle2

    'Identifies the delimiter
    If UCase(m_delimiter) = "TAB" Or UCase(m_delimiter) = "T" Then
       sDelimiter = Chr(9)
    Else
       sDelimiter = Left(m_delimiter, 1)
    End If

    Call Correct_Lf_to_CrLf(m_filepath)

    fn = openFile
    r = 0
    Do While Not EOF(fn)
        Line Input #fn, SingleLineString
        If SingleLineString = "" Then
            m_numberOfLines = r
            Exit Do
        Else
            r = r + 1
        End If
    Loop

    m_numberOfLines = r
    ReDim m_data(1 To r, 1 To 1)

    fn = openFile
    r = 0

    For r = 1 To m_numberOfLines
        Line Input #fn, SingleLineString
        Call ParseDelimitedString(SingleLineString, sDelimiter, r)
    Next r

    Close #fn
    Exit Sub
ErrorHandle:
    MsgBox Err.Description & " Error while reading " + m_filepath
    End
ErrorHandle2:
    MsgBox Err.Description & " File " & m_filepath & " does not exist!"
    End
End Sub
Private Function openFile() As Long
    Dim fn As Long, fLen As Long
    fn = FreeFile 'Gets a free file number from the operating system
    Open m_filepath For Input As #fn
    fLen = LOF(fn)
    openFile = fn
End Function
Private Sub Correct_Lf_to_CrLf(ByVal filepath As String)
    Dim DataLine As String, ReplaceFile As Boolean, fn1 As Long, fn2 As Long

    ReplaceFile = False
    fn1 = FreeFile
    Open Environ("TEMP") & "\temp.txt" For Output As #fn1
        fn2 = FreeFile
        Open filepath For Input As #fn2
            While Not EOF(fn2)
                Line Input #fn2, DataLine
                If InStr(DataLine, vbLf) > 0 Then
                    Print #fn1, Replace(DataLine, vbLf, vbNewLine)
                    ReplaceFile = True
                End If
            Wend
        Close #fn2
    Close #fn1

    'NOTE: Your original file will be replaced with the new file where Lf are replaced with CrLf
    If ReplaceFile Then
        FileSystem.FileCopy Environ("TEMP") & "\temp.txt", filepath
    End If
End Sub
Private Function ParseDelimitedString(InputString As String, sDel As String, LineNumber As Long) As Variant
    'Returns a variant array with every element in
    'InputString separated by sDel.

    Dim i As Integer, j As Integer, colCount As Long
    Dim sString As String, sChar As String * 1
    Dim ResultArray() As Variant
    Dim CheckForDelimiter As Boolean
    Dim RemoveDoubleQuotes As Boolean

    On Error GoTo ErrorHandle

    sString = ""
    colCount = 0
    RemoveDoubleQuotes = False
    CheckForDelimiter = True

    For i = 1 To Len(InputString)
        sChar = Mid$(InputString, i, 1)
        If sChar = """" Then
            CheckForDelimiter = Not CheckForDelimiter
            sString = sString & sChar
            RemoveDoubleQuotes = True
        ElseIf sChar = sDel And CheckForDelimiter Then
                colCount = colCount + 1
                Call addToResultArray(sString, ResultArray, colCount, RemoveDoubleQuotes)
                sString = ""
                RemoveDoubleQuotes = False
        Else
                sString = sString & sChar
        End If
        If i = Len(InputString) Then
            colCount = colCount + 1
            Call addToResultArray(sString, ResultArray, colCount, RemoveDoubleQuotes)
        End If
    Next i

    If LineNumber = 1 Then
        m_numberOfColumns = colCount
        ReDim m_data(1 To m_numberOfLines, 1 To m_numberOfColumns)
    End If

    For j = 1 To m_numberOfColumns
        m_data(LineNumber, j) = ResultArray(j)
    Next j

    Exit Function
ErrorHandle:
    MsgBox Err.Description & " Error in function ParseDelimitedString."
End Function
Private Function addToResultArray(sString As String, ResultArray As Variant, colCount As Long, RemoveDoubleQuotes As Boolean)
    ReDim Preserve ResultArray(1 To colCount)
    If RemoveDoubleQuotes Then
        ResultArray(colCount) = Mid(sString, 2, Len(sString) - 2)
    Else
        ResultArray(colCount) = sString
    End If
End Function
Public Sub writeToTargetRange(targetRange As Range)
    Dim i As Long, j As Long

    For i = 1 To m_numberOfLines
        For j = 1 To m_numberOfColumns
            targetRange.Cells(i, j).Value = m_data(i, j)
        Next j
    Next i
End Sub