Batch importing multiple text files to excel and comma delimiting them

[whatalegend123]

Hi guys

I am currently using a piece of equipment in work which outputs data results into text files..for measurements that are taken.I currently have imported the text files one by one to excel and comma delimited them to get at the data,using the standard excel function

I was wondering if anyone would know of / how to write a macro to automate this process

The data comes in text files named xxxx-1.txt, xxxx-2.txt etc and each text file contains roughly 200 lines of data presented like this

XXX, 111
XXX, 222
XXX, 333
XXX, 444

and i have to deal with roughly 10-20 txt files at one go.

so essentially, im looking to import 10 .txt files at a time each containing 200 lines of data which is separated by commas.

Also if i could just import the right hand column of data would ideal only the 111,222,333 part.

i found this from an old forum but it doesnt seem to work as it was written in 2002

"Sub GetTextFiles()
Dim lngCounter As Long, wbText As Workbook

On Error GoTo ErrHandler

Application.DisplayAlerts = False
Application.ScreenUpdating = False

With Application.FileSearch
.NewSearch
.FileType = msoFileTypeAllFiles
.LookIn = "C:temptext files" 'Change this to your folder name
.Execute
For lngCounter = 1 To .FoundFiles.Count
If Right(.FoundFiles(lngCounter), 4) = ".txt" Then
Workbooks.OpenText .FoundFiles(lngCounter)
ActiveSheet.UsedRange.Copy
ActiveWorkbook.Close False
ThisWorkbook.Sheets.Add
ActiveSheet.Paste
End If
Next lngCounter
End With


Application.DisplayAlerts = True
Application.ScreenUpdating = True

Exit Sub
"

 

[whatalegend123]

Hi thanks so much for all the responses

Desires you are a hero and you code was exactly what i needed... however if i could ask one more favour.. would it be possible to output all of the data into one sheet as opposed to creating a new work sheet for each text file?

 

[whatalegend123]

Haha thanks..sorry to be a pain i should have mentioned it before. but can each new text file go in a new column.. as opposed to all in one column..

i should really learn to do this stuff