Reading multiple Text Files

Balerina

New Member
Joined
Jul 15, 2014
Messages
22
Hi,
Can anyone please let me know how to get the following functionality:

My requirement is, in excel i need to provide an option to browse for the text files. User can select one or more text files at a time. Once the text files are selected, the data from the text files has to be imported to the excel sheet (creating each seperate sheet for a each selected text file, the name of the sheet will be the name of the text file).
The text files will be in a specified format with a delimiter $.

Can you please help me put on this? Do we need to write a macro for this?

Thanks in Advance.
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

ParamRay

Well-known Member
Joined
Aug 6, 2014
Messages
1,195
.
.

Try something like this:

Code:
Sub GetText()

    Dim fnm As Variant
    Dim wbk As Workbook
    Dim i As Byte
    
    fnm = Application.GetOpenFilename( _
        FileFilter:="Text Files (*.txt), *.txt", _
        Title:="Import Text File(s)", _
        MultiSelect:=True)
        
    If Not IsArray(fnm) Then Exit Sub
    
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With
    
    Set wbk = Workbooks.Add
    For i = LBound(fnm) To UBound(fnm)
        Workbooks.OpenText _
            Filename:=fnm(i), _
            DataType:=xlDelimited, _
            Other:=True, _
            OtherChar:="$"
        ActiveWorkbook.Sheets(1).Move _
            After:=wbk.Sheets(wbk.Sheets.Count)
    Next i
    
    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
    End With

End Sub
 
Last edited:

Balerina

New Member
Joined
Jul 15, 2014
Messages
22
Thank you for your reply. I tried running with the code its working fine, but one problem is it is creating a new excel sheet and importing data to that excel, I want to import the data in the same excel sheet in which i am running the macro.
Can you please help me out.

Also, can we create table to contents (may be in the very first sheet of the workbook) and display the list of sheet names being added and provide a hyperlink so that, when clicked in the first sheet it goes to the respective sheets.


Thank you in Advance.


.
.

Try something like this:

Code:
Sub GetText()

    Dim fnm As Variant
    Dim wbk As Workbook
    Dim i As Byte
    
    fnm = Application.GetOpenFilename( _
        FileFilter:="Text Files (*.txt), *.txt", _
        Title:="Import Text File(s)", _
        MultiSelect:=True)
        
    If Not IsArray(fnm) Then Exit Sub
    
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With
    
    Set wbk = Workbooks.Add
    For i = LBound(fnm) To UBound(fnm)
        Workbooks.OpenText _
            Filename:=fnm(i), _
            DataType:=xlDelimited, _
            Other:=True, _
            OtherChar:="$"
        ActiveWorkbook.Sheets(1).Move _
            After:=wbk.Sheets(wbk.Sheets.Count)
    Next i
    
    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
    End With

End Sub
 

ztodd

Board Regular
Joined
Sep 17, 2014
Messages
221
You should be able to just replace "Set wbk = Workbooks.Add" with "Set wbk = ThisWorkbook".
 

Balerina

New Member
Joined
Jul 15, 2014
Messages
22
Thank you. Its working!!

Also PLease let me know the following
Can we create table to contents (may be in the very first sheet of the workbook) and display the list of sheet names being added and provide a hyperlink so that, when clicked in the first sheet it goes to the respective sheets.
 

ztodd

Board Regular
Joined
Sep 17, 2014
Messages
221
Where in the first sheet should it go? Anything else on that sheet? This will delete everything on your first sheet and make a table of contents on it:
Code:
Sub MakeTOC()
    Sheets(1).Activate
    Rows.Delete
    [A1] = "X"
    [A2] = "Worksheet"
    Dim i
    
    For i = 2 To Worksheets.Count
        [A1].End(xlDown).Offset(1).Formula = _
          "=HYPERLINK(""[" & ThisWorkbook.Name & "]" & Sheets(i).Name & "!A1"",""" & Sheets(i).Name & """)"
    Next i
    [A1] = ""
    [A1].EntireColumn.AutoFit
End Sub
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,331
Messages
5,528,060
Members
409,799
Latest member
camronmartin

This Week's Hot Topics

Top