Import multiple text files into excel

[Wally Steadman]

I have a folder located at --> c:\precedence
Inside the folder I have about 10 files named as follows:
NC60.txt
NC61.txt
NC62.txt
NC63.txt
NC90.txt
NC91.txt
NC5801.txt
NC01.txt
NC02.txt
L64.txt

Each of the files is sent to me by different individuals,
hence the different names. Each files contains the same
formatting of information. I have created a macro that
will let me import NC60.txt into a new sheet in the open
workbook. I want to automate the process so that it will
go to the folder, import the NC60.txt to a new worksheet,
then it will loop through and add NC61.txt to a new
worksheet and so on and so forth until it has imported all
files. I have a setup file in the worksheet where I list
each file name so it might be used as a reference. Any
assistance would be appreciated.

If it is easier to discuss this or have me send you the
text files, you can email me at Walter.(REMOVE THIS)
(e-mail address removed)

Thanks for a busy soldier in Iraq.

Wally Steadman

 

[Tom Ogilvy]

Wally,
I used the OpenText method for a tab delimited file. You need to record a
macro bringing in one of the files and going through the Text import wizard,
then replace my opentext code with that code. If you add more files or
change the names, just modify the array. Or if you prefer to pick up the
list from a worksheet you can do

With Worksheets("List1")
varr = .Range(.Range("A1"),.Range("A1").End(xldown)).Value
End With

instead of the varr = Array( . . . )
which I have.

Sub AAATest()
Dim varr As Variant
Dim wkbk1 As Workbook
Dim wkbk As Workbook
Dim i As Long
Dim sh1 As Worksheet
Dim sName As String
varr = Array("NC60.txt", _
"NC61.txt", _
"NC62.txt", _
"NC63.txt", _
"NC90.txt", _
"NC91.txt", _
"NC5801.txt", _
"NC01.txt", _
"NC02.txt", _
"L64.txt")

Set wkbk = ActiveWorkbook
For i = LBound(varr) To UBound(varr)
Workbooks.OpenText FileName:="C:\precedence\" & varr(i), _
Origin:=xlWindows, _
StartRow:=1, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=True, Semicolon:=False, _
Comma:=False, Space:=False, _
Other:=False, FieldInfo:= _
Array(Array(1, 1), Array(2, 1), _
Array(3, 1), Array(4, 1))
Set wkbk1 = ActiveWorkbook
Set sh1 = wkbk.Worksheets.Add(after:= _
wkbk.Worksheets(wkbk.Worksheets.Count))
wkbk1.Worksheets(1).UsedRange.Copy _
Destination:=sh1.Range("A1")
sName = wkbk1.Name
sName = Left(sName, Len(sName) - 4)
wkbk1.Close SaveChanges:=False
sh1.Name = sName

Next
End Sub