Importing multiple text files into one work book

M

mike

I'm trying to create VBA code that will import a varying number of
text files into Excel 2000. The files will not have a .txt extension
but rather a varying numeric extension (.1, .2, .3 and so forth.
Using the macro recorder I have set up several macros that import the
text (only one file) and format it the way I want then cut and past it
into a worksheet. What I want to know how to do is import every file
in a folder, one at a time, and add the text to different rows of the
same worksheet. I have very little programming experience. Any help
will be appreciated. :)
 
B

Bill Renaud

Here are some pieces of code that I quickly copied piecemeal from another
project I did a while back. This is NOT debugged code, but should give you
some idea to get going. Basically, add a reference to the Script library to
your project (pull down the Tools menu and choose the References command),
then use it to open each data file as a text stream, read each line into a
single cell on a blank worksheet, then move down one row. Parse all of the
data in column $A out to multiple columns and format as desired (code you
have already written).

'Reference the "Microsoft Scripting Runtime" in Tools|References.

Dim varTXTFileList As Variant 'Array of filenames to process.
Dim varFileName As Variant 'Individual file name.
Dim fs As Object 'File System object.
Dim f As Object 'File object.
Dim ts As Object 'Text Stream object.
Dim ws As Worksheet
Dim rngCell As Range 'Cell to put a single imported line into.

'Create a new, empty Excel workbook to import the data into.
Set wbTextFile = Workbooks.Add 'Create a new Excel workbook.
Set ws = wbTextFile.Worksheets(1) 'Set reference to the 1st worksheet.
Set rngCell = ws.Cells(1, 1) 'Set rngCell to cell $A$1.

'Get the list of filenames (multi-select).
varTXTFileList = Application _
.GetOpenFilename( _
FileFilter:="All Files (*.*),*.*", _
FilterIndex:=1, _
Title:="Open Data Files", _
MultiSelect:=True)

For Each varFileName In varTXTFileList

'Set references to the text file and open as a text stream.
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile(Cstr(varFileName))
Set ts = f.OpenAsTextStream(1, 0) 'Open the text file for reading
'only in ASCII format.

'Import the data from the Text Stream to the Excel workbook,
'line-by-line.
Do While Not ts.AtEndOfStream 'Check end-of-file status (?).
rngCell.Value = ts.readline 'Read line of text; put in cell.
Set rngCell = rngCell.Offset(1, 0) 'Advance cell to the next row.
Loop

ts.Close 'Close text stream.
Next varFileName

'Now parse the data out into the columns using the TextToColumns method of
the Range object.
 
Top