Import multiple text files into single spreadsheet

[mik_tiff]

I'm trying (and failing) to create a macro to import a number of text files into a spreadsheet.
The files will all be in the same directory, but the number of files could change (up to 100 files at a time).
I need to import each file to a seperate worksheet and name the worksheet the same as the relevant imported file.
I'm quite new to these macro things and don't understand VB code very well

Thanks
Mik

 

[Norie]

Mik

Do you already have code?

If you do perhaps you could post it and further help can be given.

If not you should try recording a macro when you import a few of the text files.

The code generated from that can then be adapted to deal with all the files in the folder.

 

[mik_tiff]

This is a macro I recorded to import the 1st file, including adding another sheet at the end of the macro.
The next file needs to be added to the new sheet.
There is no code in the macro for naming the sheets the same as the imported file.
The directory (which won't change) is highlighted in red
The file name (which could be anything) is highlighted in yellow

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;T:\EMC\PARMS\P99 Files\Third Party Reports\2004\December\TR_31122004_DASL_M_SMAR_X_HM01_4829.txt" _
, Destination:=Range("A1"))
.Name = "TR_31122004_DASL_M_SMAR_X_HM01_4829"
.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 = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1)
.Refresh BackgroundQuery:=False
End With
Sheets.Add
End Sub