Importing multiple text files to Excel sheet and making on Excel sheet

[ajay10]

Hello all,

I am beginner and I am looking import multiple text files (2200) to excel and make a single excel sheet using VBA


Please help.. I am trying this using this 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
End Sub


Text file example

Date: 2015-1-19 9:19:55

Sta1Load.Pass: True
Sta1Load.Code: 411
Sta1Load.VisionProgram: 0
Sta2Armature.Pass: False
Sta2Armature.Code: 0
Sta2Armature.VisionProgram: 0
Sta3Weld.Pass: False
Sta3Weld.Code: 0
Sta3Weld.LaserProgram: 0
Sta3Weld.LaserPowerActual: 0
Sta3Weld.LaserPowerLL: 0
Sta3Weld.LaserPowerUL: 0
Sta3Weld.LaserPowerTarget: 0
Sta3Weld.LaserPWRpuckProgram: 0
Sta3Weld.LaserPWRpuckActual: 0
Sta3Weld.LaserPWRpuckDevMax: 0
Sta3Weld.LaserPWRpuckTarget: 0
Sta3Weld.WeldSpeedActual: 0
Sta3Weld.WeldSpeedLL: 0
Sta3Weld.WeldSpeedUL: 0
Sta4Vision.Pass: True

Required Output in the format

I want the data header in first row and the corresponding data in the rows below

Header =>Date

Date
Sta1Load.Pass
Sta1Load.
Code


Sta1Load.
VisionProgram


Sta2Armature.Pass


Sta2Armature.Code


Sta2Armature.
VisionProgram

Sta3Weld.Pass: False



2015-1-19 9:19:55
True
411
0
False
0
0
Flase



Text file2 data










Text fiel 3 data