Thread: add imported text file name above imported data

Hey,

I need to import huge amounts of text files in single excel workbooks. The text files are all located in one folder and are named as follows: "A2224.txt" "A2225.txt",... They consist of two columns of data (numbers) each.

I did find code that is working great to import all textfiles that I select and imports it in a workbook. Each imported textfile is a new sheet in the workbook with the sheet name being the name of the original textfile. The two comuns of data per text file are located in the two columns (A:B) of each imported worksheet.

the code is as follows:

Code:

Sub CombineTexts()
    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

    'for comma delimitted text files FTIR'

    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:=True, Space:=False, _
      Other:=False, OtherChar:=False
    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 = False
    Set wkbAll = Nothing
    Set wkbTemp = Nothing
    Exit Sub

ErrHandler:
    MsgBox Err.Description
    Resume ExitHandler
End Sub

now I would like to insert a column header (at B1) in each imported text file/worksheet that has the same name as the worksheet/textfile...

I am sorry that I can't solve something as simple as this not myself yet, but I am quite new to this. Any help would be most appreciated!

thank you very much!
wishes
Tom