Combine First Two rows in text file

I don't want to open the file in excel via [workbooks.open] method since opening it in excel gives the text file some weird formatting things that cause issues downstream so I would stick with the objFile.AtEndOfStream thing

Is it only 3 columns?
Also, you want to output on the same sheet with original data and new data as in the picture?
With the formats (line and color, etc...) ??

You can use code like this:

For i = 1 To 3
    Cells(1, i) = Cells(1, i) & " " & Cells(2, i)
Next i
Rows(2).Delete

Select allOpen in new window

Correct row numbers if it is not 1 and 2

If you like to do it in text file, show your code

Run the MergeFirstTwoRows macro.

Sub MergeFirstTwoRows()
Dim FF As Integer
Dim lngNextRow As Long
Dim strline As String
Dim bMerged As Boolean

FF = FreeFile

' Change this line
Open "C:\Solutions\SampleData.txt" For Input As #FF

Do While Not EOF(FF)
    Line Input #FF, strline
    Select Case lngNextRow
        Case 0, Is > 1
            If Not bMerged Then
                lngNextRow = lngNextRow + 1
            End If
            bMerged = False
            WriteData strline, lngNextRow
        Case Else
            WriteData strline, lngNextRow, True
            lngNextRow = lngNextRow + 1
            bMerged = True
    End Select
Loop

Close
End Sub
Private Sub WriteData(strline As String, lngRow As Long, Optional bMerge As Boolean = False)
Dim strParts() As String
Dim intPart As Integer

strParts = Split(strline, vbTab)
With ActiveSheet
    For intPart = 0 To UBound(strParts)
        If Not bMerge Then
            .Cells(lngRow, intPart + 1) = strParts(intPart)
        Else
            .Cells(lngRow, intPart + 1) = .Cells(lngRow, intPart + 1) & " " & strParts(intPart)
        End If
    Next
End With

End Sub

Select allOpen in new window

Hi Martin,

I tried your code on the sample file I have attached previously and it didn't work

Martin, your code is not working or showing the final result. Have you tested it on the sampledata.txt?

Hi Tuvi,

for now, its only 3 columns but that can change in the long-term
It can be the same file that has the intended change or new file, it doesn't matter

Thank you, everyone, for chipping in. you all are a lifesaver. Thank you

You’re welcome and I’m glad I was able to help.

If you expand the “Full Biography” section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange Most Valuable Expert (MVE) 2015, 2017
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2017
              Experts Exchange Top Expert VBA (current)