How to make macros that can copy the content of 100 txt files into 1 excel sheet

[disappearedin2thinair]

Hi guys,

Please help me make an excel macro that can process all the txt files in the same folder. I want to open them all and copy the content into 1 excel sheet. The filenames are different and variable. The txt files should be tab delimited.

sample filename: Paris 110303 ADJ.txt

Appreciate your immediate response.

Regards,

James

 

[syntaxed]

Welcome to MrExcel.

You may find a solution here: http://www.mrexcel.com/forum/showthread.php?t=532610

 

[disappearedin2thinair]

Thanks for the quick reply. I tried to copy the script but it gives me an error dialog box saying "run time error 429 Active x cant create object

Sub ImportTextFile()
Dim DestBook As Workbook, SourceBook As Workbook
Dim DestCell As Range
Dim RetVal As Boolean
Application.ScreenUpdating = False
Set DestBook = ActiveWorkbook
Set DestCell = ActiveCell

RetVal = Application.Dialogs(xlDialogOpen).Show("*.txt")

Do While RetVal = True
Set SourceBook = ActiveWorkbook
Range(Range("A1"), Range("A1").SpecialCells(xlLastCell)).Copy
DestBook.Activate
DestCell.PasteSpecial Paste:=xlValues
SourceBook.Close False
Set DestCell = Cells(DestCell.Row + 1, DestCell.Column)
RetVal = Application.Dialogs(xlDialogOpen).Show("*.txt")
Loop
Application.ScreenUpdating = True
End Sub


this is the script i used

 

[syntaxed]

Sorry to hear that. Try this instead:

Sub ImportTextFile()
    Dim DestBookName, SourceBookName
    Dim RetVal As Boolean
    Application.ScreenUpdating = False
    
    DestBookName = ActiveWorkbook.Name    
    RetVal = Application.Dialogs(xlDialogOpen).Show("*.txt")
    
    Do While RetVal = True
        SourceBookName = ActiveWorkbook.Name
        ActiveSheet.UsedRange.Copy
        Workbooks(DestBookName).Activate
        Cells(ActiveSheet.UsedRange.Rows.Count + 1, 1).Select
        ActiveSheet.Paste
        Workbooks(SourceBookName).Close False
        RetVal = Application.Dialogs(xlDialogOpen).Show("*.txt")
    Loop
    Application.ScreenUpdating = True
End Sub