Importing Text files into excel macro

[excel_2009]

Hi excel gurus,

i stumbled across the following coding:

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")

  
    If RetVal = False Then Exit Sub
   
    Set SourceBook = ActiveWorkbook
 
    Range(Range("A1"), Range("A1").SpecialCells(xlLastCell)).Copy
   
    DestBook.Activate
    DestCell.PasteSpecial Paste:=xlValues

    SourceBook.Close False

End Sub

The coding allows me to import a text file sucessfully into an excel sheet, however i'd like to somehow adapt the coding so that multiple text files can be imported, is that possible?

Thank you

 

[Andrew Poulsom]

Look here:

http://www.rondebruin.nl/txtcsv.htm

 

[excel_2009]

Hi Andrew,

Thank you so much it's perfect! just one question though, is it at all possible to insert the text files into one sheet as opposed to multiple sheets?

 

[syntaxed]

An alternative, using a slight modification to the code you supplied:

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

Each text file will go to a new row of the same sheet. To stop importing files, click Cancel in the Dialog Box.

 

[Andrew Poulsom]

This shows you how to create a summary sheet from multiple sheets:

http://www.rondebruin.nl/copy2.htm