Ianwu,

The following VBA sub will open each .txt file in folder C:\MyFolder in
turn, and import each line in each file horizontally, so no need to
transpose afterwards. Open a new workbook, right-click on a sheet tab and
select View Code; when taken to the VBA window, select the file you want to
save the code in in the upper left panel (the new workbook, or, Personal.xls
so it is globally available), and go to menu item Insert > Module. Pste the
code below in the right hand side panel, and change the folder from
C:\MyFolder to your actual folder name. Run the code (F5 in the VBA window
or Tools > Macro > Macros while in the spreadsheet) and it's done.

Sub Import_Text_Files()
   Dim fs, f, f1, vLine
   Dim i As Long, j As Long
   i = 0
   Range("A1").Select
   fldr = "C:\MyFolder\"
   Set fs = CreateObject("Scripting.FileSystemObject")
   Set f = fs.getfolder(fldr)
   Set f1 = f.Files
   For Each f2 In f1
       filenam = fldr & f2.Name
       If Right(filenam, 4) = ".txt" Then
           j = 0
           Open filenam For Input As #1
               Do Until EOF(1)
                   Line Input #1, vLine
                   ActiveCell.Offset(i, j).Value = vLine
                   j = j + 1
               Loop
           Close #1
           i = i + 1
       End If
   Next
End Sub

HTH,
Nikos