importing multiple text files in to multiple work sheets with text file names .

[narall]

Hi, I am looking for Excel VB code where i can import data from multiple text(notepad) files into multiple worksheets of a new excel workbook. Also the name of the tabs should be as the names of the text files. Any Suggestions???

 

[Logit]

Maybe these will get you started :

First project / workbook example :

Option Explicit
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:=True, Space:=False, _
    Other:=True, 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 = True
    Set wkbAll = Nothing
    Set wkbTemp = Nothing
    Exit Sub
    
ErrHandler:
    MsgBox Err.Description
    Resume ExitHandler
End Sub

Second project / workbook example :

Option Explicit

Sub Sample()
    Dim MyData As String
    Dim lineData() As String, strData() As String, myFile As String
    Dim i As Long, rng As Range

    On Error Resume Next
    
    ' This is the line to edit for the file location and name
    myFile = "C:\Users\gagli\Desktop\DF.txt"

    Open myFile For Binary As #1
    MyData = Space$(LOF(1))
    Get #1, , MyData
    Close #1
    
    ' Split into wholes line
    lineData() = Split(MyData, vbNewLine)
    Set rng = Range("A2")
    
    ' For each line
    For i = 0 To UBound(lineData)
    
        ' Split the line
        strData = Split(lineData(i), "|")
        
        ' Write to the sheet
        rng.Offset(i, 0).Resize(1, UBound(strData) + 1) = strData
        
    Next
    
    Range("A:A").Select
    
    'Text To Columns applied to the pasted data
     Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, OtherChar _
        :="|", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
        1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1)), _
        TrailingMinusNumbers:=True
        
    'Adjust all of the columns so the data presentation is appealing
    Worksheets("Sheet1").Columns("A:Z").AutoFit
    
    'Select Cel A1
    Range("A1").Select
    
End Sub

 

[narall]

Surely i will try
Please let me know " if you provide a recorded video with example. It will help me. Because i dont know much about vba.
My only requerment is " importing multiple text files in to multiple work sheets with text file names" . Thats it no matter what.
If you help me with example and video . It will help.

 

[Logit]

.
With VBA code :

No VBA code :

 

[minhys1]

Please help me: I want the data of many .txt files, after being included in the excel file, will only be in a single sheet, the duplicate title is removed, the separator in the text file is a comma. Example text file 1

"col1,col2

Minh, Nam"



text file 2

"col1,col2

Lan, Nu

Long, Nam"



* desired result:

col 1 col 2

Minh Nam

Lan Nu

Long Nam