querytable connection

[Guest]

Excel help files for version 2002 give the following example:



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

 

[Claud Balls]

Sub import()
On Error Resume Next
For i = 1 To 5
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" _ &
ActiveWorkbook.Path & _
"\equiv" & i & ".txt", Destination:=Range("A1"))
.PreserveFormatting = True
.RefreshStyle = xlInsertDeleteCells
.SaveData = True
.AdjustColumnWidth = True
.TextFileStartRow = 1
.TextFileParseType = xlFixedWidth
.TextFileColumnDataTypes = Array(xlTextFormat, xlSkipColumn,
xlGeneralFormat)
.TextFileFixedColumnWidths = Array(5, 4)
.Refresh BackgroundQuery:=False
End With
Next i
End Sub