Merge Functions‎ > ‎

TXTs to Columns

"I have a folder full of text files, I need to import them all into a single Excel sheet, putting each file in a column of its own."

  1. Files are text file with no delimiters 
  2. All files in one folder
  3. Each file placed in a column
  4. Filename the text came from is placed at the top of each column


Option Explicit

Sub ImportManyTXTIntoColumns()
'Author:    Jerry Beaucaire
'Date:      2/24/2012
'Summary:   From a specific folder, import TXT files 1 file per column

Dim fPath As String, fTXT As String
Dim wsTrgt As Worksheet, NC As Long

Application.ScreenUpdating = False
fPath = "C:\2010\"                      'path to files, remember the final \
Set wsTrgt = ThisWorkbook.Sheets.Add    'new sheet for incoming data
NC = 1                                  'first column for data

fTXT = Dir(fPath & "*.txt")             'get first filename

    Do While Len(fTXT) > 0              'process one at a time
                                        'open the file in Excel
        Workbooks.OpenText fPath & fTXT, Origin:=437
                                        'put the filename in the target column
        wsTrgt.Cells(1, NC) = ActiveSheet.Name
                                        'copy column A to new sheet
        Range("A:A").SpecialCells(xlConstants).Copy wsTrgt.Cells(2, NC)

        ActiveWorkbook.Close False      'close the source file
        NC = NC + 1                     'next column
        fTXT = Dir                      'next file
Application.ScreenUpdating = True
End Sub

Nothing says "thanks" like a steak dinner!
PayPal - The safer, easier way to pay online!