Sub Importfiles()
Dim fName as variant, sSheet as Variant
Dim i as long, fn as String
Dim rng as Range
fname = Array( _
"C:\text\file1.txt", _
"C:\text\File2.txt", _
"C:\text\File2.txt")
sSheet = Array("sheet1", _
"sheet2", _
"sheet3")
for i = lbound(fname) to ubound(fname)
fn = fname(i)
set sh = worksheets(sSheet(i))
set rng = sh.Range("B9")
ImportText sh, fn, rng
Next
End sub




Public Function Importtext( sh as Worksheet, sName as String, rng as Range)

With sh.QueryTables.Add(Connection:="TEXT;" & _
sName, Destination:=rng)
.Name = "aaa_tab"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1)
.Refresh BackgroundQuery:=False
End With
End Sub


--
Regards,
Tom Ogilvy

"clui" wrote in message
...

Thanks, Tom. Now what if I need to import multiple text files into
separate worksheets? I should not have to repeat the complete piece of
code for each import. I tried several ways, but none worked. Please
help.


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to
creating financial statements