Import Multiple Text Files Into Single Worksheet

Leith,

Thank you for your in-depth response.

I notice the title shows up in Column A (excellent!) while the rest of the text shows up in multiple columns (instead of all in cell B1).

Do you know how to move all the text into once cell only?

I've attached the workbook with two sheets:

Sheet #1 is what the macro creates
Sheet #2 is what we need it to look like

(Note: I copied the same article twice just for demonstration purposes)

Again, thank you SO much for your help!

Best,

Coz

Originally Posted by Leith Ross

Hello Coz,

Here is the updated macro. The attached workbook has a button on "sheet1" to run the macro.

Macro Code to Read Text Files

Sub ReadTextFiles()

  Dim CellData As String
  Dim FileName As String
  Dim FilePath As String
  Dim LineCnt As Long
  Dim R As Long
  Dim Text As String
  Dim Wks As Worksheet
  
    R = 1
    Set Wks = Worksheets("Sheet1")
    
    FilePath = "C:\Users\Platinum\Desktop\ExcelMacro"
    
    FileName = Dir(FilePath & "\*.txt")
    
      Do While FileName <> ""
        LineCnt = 0
        CellData = ""
        N = FreeFile
        Open FilePath & "\" & FileName For Input As #N
          Do While Not EOF(N)
            Line Input #N, Text
            LineCnt = LineCnt + 1
            If Text = "" Then Text = " " & vbLf
            If LineCnt = 1 Then
              Wks.Cells(R, "A").Value = Text
            Else
               CellData = CellData & Text
            End If
          Loop
          Wks.Cells(R, "B").Value = CellData
        Close #N
        R = R + 1
        FileName = Dir()
      Loop
    
End Sub

Leith, you are a GOLDEN GOD!

Thank you, thank you, thank you!

Happy New Year!

-Coz