Import multiple text files (Macro)
 

I've got multiple text files with data in a consistent format like
this:

Header1: Text1
Header2: Text2
Header3: Text3
etc.


I'd like to import all the text files. I'd also like to change the
direction of the data so that it's horizontal rather than vertical. The

Header information can be disregarded. I recorded a macro that imports
the data but I don't know how to modify it to do what I want.


Can someone help? Thanks


Here's the Macro. I'd like the destination to be consecutive and there
is no rhyme nor reason to the text name (m07162717).


Sub Import()
'
' Import Macro
' Macro recorded 9/18/2006 by baumgabt
'


'
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Documents and Settings\BaumgaBT\Desktop\WSP
MSG\m07162717.032", _
Destination:=Range("A1"))
.Name = "m07162717"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = ":"
.TextFileColumnDataTypes = Array(9, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub