rhkm
07-18-2013, 01:50 AM
Hi everyone
I am assigned to find a way to be able to import a several text files into a single excel file in different sheets. So far I have been working on with this code I found online written by Allen Wyatt
Below is the code
Sub CombineTextFiles()
Dim FilesToOpen
Dim x As Integer
Dim wkbAll As Workbook
Dim wkbTemp As Workbook
Dim sDelimiter As String
On Error GoTo ErrHandler
Application.ScreenUpdating = False
sDelimiter = "|"
FilesToOpen = Application.GetOpenFilename _
(FileFilter:="Text Files (*.txt), *.txt", _
MultiSelect:=True, Title:="Text Files to Open")
If TypeName(FilesToOpen) = "Boolean" Then
MsgBox "No Files were selected"
GoTo ExitHandler
End If
x = 1
Set wkbTemp = Workbooks.Open(FileName:=FilesToOpen(x))
wkbTemp.Sheets(1).Copy
Set wkbAll = ActiveWorkbook
wkbTemp.Close (False)
wkbAll.Worksheets(x).Columns("A:A").TextToColumns _
Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, Semicolon:=False, _
Comma:=False, Space:=False, _
Other:=True, OtherChar:="|"
x = x + 1
While x <= UBound(FilesToOpen)
Set wkbTemp = Workbooks.Open(FileName:=FilesToOpen(x))
With wkbAll
wkbTemp.Sheets(1).Move After:=.Sheets(.Sheets.Count)
.Worksheets(x).Columns("A:A").TextToColumns _
Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, Semicolon:=False, _
Comma:=False, Space:=False, _
Other:=True, OtherChar:=sDelimiter
End With
x = x + 1
Wend
ExitHandler:
Application.ScreenUpdating = True
Set wkbAll = Nothing
Set wkbTemp = Nothing
Exit Sub
ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub
The code works nicely but there are a few modifications that I need:
1. When all the text files needed are successfully imported to the excel file, the cells containing the actual data are not set to contain numbers. even though they have numbers in them, the data type is not number.
2. The format of the text files is as the following
Frame reading 1 reading 2
1 numval numval
2 numval numval
and so on. "numval" meaning numerical value. I need to add another column between 'Frame' and 'reading 1' which contain the values from 1/80, 2/80, 3/80 and so on until the last row. And also having the column title as 'time' would be awesome.
3. Finally, I need to add a graph to the sheets. I need to add a graph of time vs reading 1 & reading 2. The graph needs to be a line graph. One thing that needs to be noted is that some of the text files have only two readings as in the example, but some of them has four readings.
Here is a few examples of the text files
10270
Please help me in any way. I would like to thank you in advance
Greetings
Reuben
I am assigned to find a way to be able to import a several text files into a single excel file in different sheets. So far I have been working on with this code I found online written by Allen Wyatt
Below is the code
Sub CombineTextFiles()
Dim FilesToOpen
Dim x As Integer
Dim wkbAll As Workbook
Dim wkbTemp As Workbook
Dim sDelimiter As String
On Error GoTo ErrHandler
Application.ScreenUpdating = False
sDelimiter = "|"
FilesToOpen = Application.GetOpenFilename _
(FileFilter:="Text Files (*.txt), *.txt", _
MultiSelect:=True, Title:="Text Files to Open")
If TypeName(FilesToOpen) = "Boolean" Then
MsgBox "No Files were selected"
GoTo ExitHandler
End If
x = 1
Set wkbTemp = Workbooks.Open(FileName:=FilesToOpen(x))
wkbTemp.Sheets(1).Copy
Set wkbAll = ActiveWorkbook
wkbTemp.Close (False)
wkbAll.Worksheets(x).Columns("A:A").TextToColumns _
Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, Semicolon:=False, _
Comma:=False, Space:=False, _
Other:=True, OtherChar:="|"
x = x + 1
While x <= UBound(FilesToOpen)
Set wkbTemp = Workbooks.Open(FileName:=FilesToOpen(x))
With wkbAll
wkbTemp.Sheets(1).Move After:=.Sheets(.Sheets.Count)
.Worksheets(x).Columns("A:A").TextToColumns _
Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, Semicolon:=False, _
Comma:=False, Space:=False, _
Other:=True, OtherChar:=sDelimiter
End With
x = x + 1
Wend
ExitHandler:
Application.ScreenUpdating = True
Set wkbAll = Nothing
Set wkbTemp = Nothing
Exit Sub
ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub
The code works nicely but there are a few modifications that I need:
1. When all the text files needed are successfully imported to the excel file, the cells containing the actual data are not set to contain numbers. even though they have numbers in them, the data type is not number.
2. The format of the text files is as the following
Frame reading 1 reading 2
1 numval numval
2 numval numval
and so on. "numval" meaning numerical value. I need to add another column between 'Frame' and 'reading 1' which contain the values from 1/80, 2/80, 3/80 and so on until the last row. And also having the column title as 'time' would be awesome.
3. Finally, I need to add a graph to the sheets. I need to add a graph of time vs reading 1 & reading 2. The graph needs to be a line graph. One thing that needs to be noted is that some of the text files have only two readings as in the example, but some of them has four readings.
Here is a few examples of the text files
10270
Please help me in any way. I would like to thank you in advance
Greetings
Reuben