My Excel reads a CSV file to get data for a grid table.

"header", "header", "header", "header"
"value1", 
"value2", "value3", "value4" 
"value5", "value6", "value7", "value8"
"value9", "value10", "value11", "value12" 

I want to read the second and third line of the CSV as the first row of the grid table. Other lines are read one by one.

My code is:

Dim FileName As String, folder As String    
    folder = ThisWorkbook.Path & "\"
    FileName = Dir(ThisWorkbook.Path & "\*.csv")


    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

My approach: I am trying to modify the csv file with a new one that will have the second and third line merged as the second line.

filePath = folder & fileName
 
 Dim fName As String, fso As Object, fsoFile As Object, txt As Variant

    Set fso = CreateObject("Scripting.FileSystemObject")

    Set fsoFile = fso.OpenTextFile(filePath, 1)
    txt = fsoFile.ReadAll
    fsoFile.Close

    txt = Split(txt, vbNewLine)

    txt(2 - 1) = "some text with special characters like & Char(44) & and & Chr(10) & and so on"

    Set fsoFile = fso.OpenTextFile(filePath, 2)
    fsoFile.Write Join(txt, vbNewLine)
    fsoFile.Close

the problem is that the grid table displays the special characters as & Char(44) & and & Char(10) & inside the cells...

CodePudding user response：

Three methods for combining 2nd and 3rd lines

Sub merge23()

    Dim fso As Object, tsIn, tsOut
    Dim s As String
    Set fso = CreateObject("Scripting.Filesystemobject")
    Set tsIn = fso.OpenTextFile("C:\temp\test.csv", 1)
    Set tsOut = fso.CreateTextFile("C:\temp\test1.csv", 1)
    
    ' method 1
    Do While tsIn.AtEndOfLine <> True
        s = tsIn.readline
        If tsIn.Line <> 3 Then
            s = s & vbCrLf
        End If
        tsOut.write s
    Loop
    tsIn.Close
    tsOut.Close
    
    ' method 2
    Set tsIn = fso.OpenTextFile("C:\temp\test.csv", 1)
    s = tsIn.readall
    tsIn.Close
    s = Replace(s, vbCrLf, "~#~#~", 1, 1) 'mark 1st crlf
    s = Replace(s, vbCrLf, "", 1, 1) ' replace 2nd
    s = Replace(s, "~#~#~", vbCrLf, 1, 1) ' replace 1st crlf
    Set tsOut = fso.CreateTextFile("C:\temp\test2.csv", 1)
    tsOut.writeline s
    
    ' method 3 regex
    Dim regex
    Set regex = CreateObject("vbscript.regexp")
    With regex
       .Global = False
       .MultiLine = True
       .Pattern = "^(.*\r\n.*)\r\n" ' 2nd crlf
    End With
    
    Set tsIn = fso.OpenTextFile("C:\temp\test.csv", 1)
    s = tsIn.readall
    tsIn.Close
    
    Set tsOut = fso.CreateTextFile("C:\temp\test3.csv", 1)
    s = regex.Replace(s, "$1")
    tsOut.writeline s
    tsOut.Close
End Sub