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.
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
.
.

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:
Upvote 0
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
 
Upvote 0
You should be able to just replace "Set wbk = Workbooks.Add" with "Set wbk = ThisWorkbook".
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,937
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top