I have multiple txt files (close 1000) and I want to import to excel. I used text import wizard then choose fixed width, my question is how to use the same format for each file that I don't need to adjust everytime.
This is the example link : http://www.fhwa.dot.gov/bridge/nbi/1992/AL92.txt
This is the record format: http://www.fhwa.dot.gov/bridge/nbi/format.cfm
To save you a bit of time I prepared the array (from the format web page linked above) for the querytables/import for you based on @RonRosenfeld's advice and quickly did a text file import (which I recorded). Have to paste in here cause it is too big for comments. You would, of course, have to edit to suit.
With ActiveSheet.QueryTables.Add(Connection:="TEXT;C:\AL92.txt", Destination:=Range("$A$1"))
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlFixedWidth
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileFixedColumnWidths = Array(3, 15, 1, 1, 1, 5, 1, 2, 3, 5, 24, 1, 18, 25, 4, 7, 1, 10, 2, 8, 9, 3, 1, 2, 2, 2, 4, 2, 2, 6, 4, 1, 4, 1, 2, 1, 1, 1, 1, 1, 1, 1, 4, 5, 1, 1, 1, 1, 2, 1, 2, 3, 4, 3, 5, 6, 3, 3, 4, 4, 4, 1, 4, 1, 3, 3, 1, 1, 1, 1, 1, 1, 3, 1, 3, 1, 1, 1, 1, 1, 1, 2, 1, 6, 4, 2, 3, 3, 3, 4, 4, 4, 6, 6, 6, 4, 3, 2, 15, 1, 1, 1, 1, 1, 1, 4, 1, 1, 1, 1, 2, 1, 1, 1, 1, 6, 4, 4)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With