Hi Manny,

Many thanks for the code. I have a little variation and wonder if you could help:

In my case, I would like for the title of the excel file to be on a Cell C1 while the content of that text file in in the adjoining Cell C2 (ie not spread across the sheet)

Thanks for your anticipated support

RapidAsia... you owe Manny one massive thanks !

Hi, rapidasia,

Well, you found the VBE. Next step is to insert a module into your project. Paste the following macro into that module:

' ************************* ' ************************* '

Sub ImportDataFromTxtFiles()

Application.ScreenUpdating = False

Dim oFileDialog As FileDialog
Dim LoopFolderPath As String
Dim oFileSystem As FileSystemObject
Dim oLoopFolder As Folder
Dim oFilePath As File
Dim oFile As TextStream
Dim RowN As Long
Dim ColN As Long

On Error GoTo ERROR_HANDLER

Set oFileDialog = Application.FileDialog(msoFileDialogFolderPicker)

RowN = 1
ColN = 3 ' Column C

With oFileDialog
    If .Show Then
        ActiveSheet.Columns(ColN).Cells.Clear
       
        LoopFolderPath = .SelectedItems(1) & "\"
       
        Set oFileSystem = CreateObject("Scripting.FileSystemObject")
        Set oLoopFolder = oFileSystem.GetFolder(LoopFolderPath)
       
        For Each oFilePath In oLoopFolder.Files
            Set oFile = oFileSystem.OpenTextFile(oFilePath)
           
            With oFile
                Do Until .AtEndOfStream
                    ActiveSheet.Cells(RowN, ColN).Value = .ReadLine
                    RowN = RowN + 1
                Loop
               
                .Close
            End With
        Next oFilePath
    End If
End With

EXIT_SUB:
    Set oFilePath = Nothing
    Set oLoopFolder = Nothing
    Set oFileSystem = Nothing
    Set oFileDialog = Nothing
   
    Application.ScreenUpdating = True
   
    Exit Sub

ERROR_HANDLER:
    ' Some code for error handling
    Err.Clear
    GoTo EXIT_SUB

End Sub

' ************************* ' ************************* '

To run a macro go to:

 - in Excel 2003: Tools -> Macro -> Macros...

 - in Excel 2007: Ribbon -> View -> Macros

Select ImportDataFromTxtFiles macro if it is not and press Run.

What macro does after is run:

 - A window to browse your folder with the text files is opened.

 - Clear all data in active sheet column C.

 - Reads each file and insert data into active sheet column C. If there is more than one line in the file, it will be inserted too in the next cell of column C.

Best regards.