Load CSV with line breaks

Question

I am loading [this CSV][1] to Excel with VBA. I am validating the CSV with [CSV Lint][2] and I get three errors with line breaks.

My code is:

 Dim ws As Worksheet 
    Set ws = ActiveSheet
    
        Dim fileName As String, folder As String    
        folder = ThisWorkbook.Path & "\"
        fileName = Dir(ThisWorkbook.Path & "\*.csv")
    
        ActiveCell.Offset(1, 0).Range("A6").Select
    
    With ActiveSheet.QueryTables _
            .Add(Connection:="TEXT;" & folder & fileName , Destination:=ActiveCell)
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = 850
            .TextFileStartRow = 1
            .TextFileParseType = xlDelimited
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            .TextFileConsecutiveDelimiter = False
            .TextFileTabDelimiter = False
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = True
            .TextFileSpaceDelimiter = False
            .TextFileColumnDataTypes = Array(1, 1, 1, 1)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=True
            
        End With

Is it possible to remove the line breaks without modifying the CSV file to a new one? I have tried many macros but nothing seems to work.

If I have to modify the CSV to a new one I have this code:

Dim fso As Object, tsIn, tsOut
Dim s As String
Dim filePath As String
Dim newCSV As String
      
filePath = folder & fileName
newCSV = folder & "report.csv"

Set fso = CreateObject("Scripting.Filesystemobject")
Set tsIn = fso.OpenTextFile(filePath, 1)
Set tsOut = fso.CreateTextFile(newCSV, 1)



Do While tsIn.AtEndOfLine <> True
    s = tsIn.readline
    s = Replace(s, vbCrLf, "")
    tsOut.write s
Loop
tsIn.Close
tsOut.Close
    
Kill filePath

but it removes all the line breaks in the CSV file.

How can I search for the line breaks that affect my Excel?

Answer 1

Open the CSV as a workbook and copy the data into your active sheet. Any quoted line breaks will appear within cells.

Option Explicit

Sub loadcsv()

    Dim ws As Worksheet, wbCSV As Workbook, n As Long
    Dim fileName As String, folder As String
    
    folder = ThisWorkbook.Path & "\"
    fileName = Dir(folder & "*.csv")
    
    Set ws = ActiveSheet
    Set wbCSV = Workbooks.Open(folder & fileName, False, True)
    With wbCSV.Sheets(1)
        n = .UsedRange.Rows.Count
        .UsedRange.Copy ws.Range("A7")
    End With
    wbCSV.Close False
    ws.Columns("A:T").AutoFit
    ws.Rows("7").Resize(n).AutoFit
    MsgBox n & " rows loaded from " & folder & fileName, vbInformation
   
End Sub