テキストファイルやＣＳＶファイルなどを一括でシートに取り込むサンプルです。
上段と下段はほとんど同じものですが、 もしレコードの最後が通常の改行コードCRLFではなくLFで終わっていた場合、下段の方法で分割すれば同じコードが使えるという例です。

Public Sub Sample_1()
'CRLFレコード

    Dim FileName As String
    Dim N As Integer
    Dim rngDest As Range
    Dim LineData As Variant
    Dim FieldData As Variant
    Dim inData As String
    Dim i As Long
    Dim vDelimiter As Variant
    

    Set rngDest = Worksheets("Sheet1").Range("A1")
    
    '読み込み
    FileName = ThisWorkbook.Path & "\test.csv"
    N = FreeFile
    Open FileName For Input As #N
    inData = InputB(LOF(N), N)
    inData = StrConv(inData, vbUnicode)
    Close #N
    
    'シートへ出力
    vDelimiter = ","
    LineData = Split(inData, vbCrLf)	'--->改行コードによる分割（1）
    For i = 0 To UBound(LineData)
        FieldData = Split(CStr(LineData(i)), vDelimiter)
        On Error Resume Next
        rngDest.Resize(1, UBound(FieldData) + 1).Value = FieldData
        Set rngDest = rngDest.Offset(1)
        On Error GoTo 0
    Next
  
    Set rngDest = Nothing

End Sub

Public Sub Sample_2()
'LFレコード

    Dim FileName As String
    Dim N As Integer
    Dim rngDest As Range
    Dim LineData As Variant
    Dim FieldData As Variant
    Dim inData As String
    Dim i As Long
    Dim vDelimiter As Variant
    

    Set rngDest = Worksheets("Sheet1").Range("A1")
    
    '読み込み
    FileName = ThisWorkbook.Path & "\test.csv"
    N = FreeFile
    Open FileName For Input As #N
    inData = InputB(LOF(N), N)
    inData = StrConv(inData, vbUnicode)
    Close #N
    
    'シートへ出力
    vDelimiter = ","
    LineData = Split(inData, vbLf)	'--->改行コードによる分割（2）
    For i = 0 To UBound(LineData)
        FieldData = Split(CStr(LineData(i)), vDelimiter)
        On Error Resume Next
        rngDest.Resize(1, UBound(FieldData) + 1).Value = FieldData
        Set rngDest = rngDest.Offset(1)
        On Error GoTo 0
    Next
  
    Set rngDest = Nothing

End Sub