change code to only get text files

AC

Board Regular
Joined
Mar 21, 2002
Messages
153
The code below gets all the files in a folder and puts them in excel, can it be changed so it only gets the text, .TXT, files.
Excel 2003

Code:
Sub ReadFilesIntoActiveSheet()
'Must References Microsoft Scripting Runtime
    Dim fso As FileSystemObject
    Dim folder As folder
    Dim file As file
    Dim FileText As TextStream
    Dim TextLine As String
    Dim Items() As String
    Dim i As Long
    Dim cl As Range
 
    ' Get a FileSystem object
    Set fso = New FileSystemObject
 
    ' get the directory you want
    Set folder = fso.GetFolder("C:\Documents and Settings\Computer1\Desktop\test\")
    
    ' set the starting point to write the data to
    Set cl = ActiveSheet.Cells(1, 1)
 
    ' Loop thru all files in the folder
    For Each file In folder.Files
        ' Open the file
        Set FileText = file.OpenAsTextStream(ForReading)
 
        ' Read the file one line at a time
        Do While Not FileText.AtEndOfStream
            TextLine = FileText.ReadLine
 
            ' Parse the line into | delimited pieces
            Items = Split(TextLine, "|")
 
            ' Put data on one row in active sheet
            For i = 0 To UBound(Items)
                cl.Offset(0, i).Value = Items(i)
            Next
 
            ' Move to next row
            Set cl = cl.Offset(1, 0)
        Loop
 
        ' Clean up
        FileText.Close
    Next file
 
    Set FileText = Nothing
    Set file = Nothing
    Set folder = Nothing
    Set fso = Nothing
    

End Sub
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Try...

Code:
[font=Verdana]        [color=darkblue]If[/color] fso.GetExtensionName(file) = "txt" [color=darkblue]Then[/color]
    
           [color=green]' Open the file[/color]
           Set FileText = file.OpenAsTextStream(ForReading)

           .
           .
           .
    
           [color=green]' Clean up[/color]
           FileText.Close
           
        [color=darkblue]End[/color] [color=darkblue]If[/color]
           [/font]
 
Upvote 0

Forum statistics

Threads
1,224,603
Messages
6,179,853
Members
452,948
Latest member
UsmanAli786

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