Importing text files into same workbook.

[rkckjk]

I need to import approx. 25 text files into an Excel workbook from a
directory. Import each text file into a different worksheet of the
workbook. Starting in row 2.

The name of the worksheet should correspond to the name of the text
file.

The records in the text files are delimited bt the pipe"|" symbol.

Thanks

 

[Ron de Bruin]

See this example
http://www.rondebruin.nl/txtcsv.htm

 

[Joel]

Try this code. Change Folder name as required

Sub add_files()

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
Const Delimiter = ","
Set fsread = CreateObject("Scripting.FileSystemObject")

Folder = "C:\temp"

First = True
Do
If First = True Then
FName = Dir(Folder & "\" & "*.txt")
First = False
Else
FName = Dir()
End If
If FName <> "" Then
ThisWorkbook.Worksheets.Add
Set NewSht = ActiveSheet
RowCount = 2

Set fread = fsread.GetFile(FName)
Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault)
NewSht.Name = Left(fread.shortname, Len(fread.shortname) - 4)

Do While tsread.atendofstream = False

InputLine = tsread.ReadLine

'extract comma seperated data
ColumnCount = 1
Do While InputLine <> ""
DelimiterPosition = InStr(InputLine, Delimiter)
If DelimiterPosition > 0 Then
Data = Trim(Left(InputLine, DelimiterPosition - 1))
InputLine = Mid(InputLine, DelimiterPosition + 1)
Else
Data = Trim(InputLine)
InputLine = ""
End If

NewSht.Cells(RowCount, ColumnCount) = Data
ColumnCount = ColumnCount + 1
Loop
RowCount = RowCount + 1
Loop

tsread.Close

End If

Loop While FName <> ""
End Sub