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