Excel help files for version 2002 give the following example:

This example imports a fixed-width text file into a new query table on the first worksheet in the first workbook. The first column in the text file is five characters wide and is imported as text. The second column is four characters wide and is skipped. The remainder of the text file is imported into the third column and has the General format applied to it.



Set shFirstQtr = Workbooks(1).Worksheets(1)
Set qtQtrResults = shFirstQtr.QueryTables _
.Add(Connection := "TEXT;C:\My Documents\19980331.txt", _
Destination := shFirstQtr.Cells(1, 1))
With qtQtrResults
.TextFileParseType = xlFixedWidth
.TextFileFixedColumnWidths = Array(5, 4)
.TextFileColumnDataTypes = _
Array(xlTextFormat, xlSkipColumn, xlGeneralFormat)
.Refresh
End With


I have tried modifying the file path to be automatically set as the the
current path (thisworkbook.path) etc. without success.

Ideally I would like to set the above up as a called process for all
equiv?.txt files in the current directory (where ? equals 1 to 5).

Alternately,

The following portion of code courtesy John Walkenbach's Excel 2000 power
programming imports and parses multiple text files (do while loops not shown
here) with the opentext command, however these are all opened into separate
files rather than one single file:

Workbooks.OpenText FileName:=FileName, _
Origin:=xlWindows, _
StartRow:=1, _
DataType:=xlFixedWidth, _
FieldInfo:= _
Array(Array(0, 1), Array(3, 1), Array(12, 1))

The above would be perfect if modified to import to multiple sheets (with
same names as originating text file) within a single excel file.

Thanks for any help.

Jason