Associate
- Joined
- 16 Jun 2007
- Posts
- 279
- Location
- mysterious archipelago
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
"
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
"