Importing text files into same workbook.

R

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
 
J

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top