• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1801
  • Last Modified:

Excel VBA File Type Filter

Experts,

I currently use a code which lists all the files in a folder/sub folder. Is there a way to modify the code to filter and show only ".txt" or "xls" files? I know currently I can use Autofilter but it is slow because the code first has to retrieve everything. I am hoping that applying the filter will speed up the code.

Thanks,
Fez
Dim iRow
Sub ListFiles()
    iRow = 11
    Call ListMyFiles(Range("C7"), Range("C8"))
End Sub

Sub ListMyFiles(mySourcePath, IncludeSubfolders)
    Set MyObject = New Scripting.FileSystemObject
    Set mySource = MyObject.GetFolder(mySourcePath)
    On Error Resume Next
    Application.ScreenUpdating = False
    
    For Each myFile In mySource.Files
        iCol = 2
        Cells(iRow, iCol).Value = myFile.Path
        iCol = iCol + 1
        Cells(iRow, iCol).Value = myFile.Name
        iCol = iCol + 1
        Cells(iRow, iCol).Value = myFile.Type
        iCol = iCol + 1
        Cells(iRow, iCol).Value = myFile.Size
        iCol = iCol + 1
        Cells(iRow, iCol).Value = myFile.DateLastModified
        iRow = iRow + 1
    Next
    If IncludeSubfolders Then
        For Each mySubFolder In mySource.SubFolders
            Call ListMyFiles(mySubFolder.Path, True)
        Next
    End If
    Application.ScreenUpdating = True
End Sub

Open in new window

0
bose3
Asked:
bose3
1 Solution
 
wobbledCommented:
Can't you just put an IF statement in to check the file type

For Each myFile In mysource.Files
        If myFile.Type = "txt" Or myFile.Type = "xls" Then
    ......



Or use the GetExtensionName(path) filesytem object to again to a check

If GetExtensionName(myFile.Path) = "txt" or GetExtensionName(myFile.Path) = xls Then
........
0
 
kenyayvetteCommented:
I have code that modifies all of the excel files only in a given directory.  In order to do this, I used

Fname=Dir(Fpath & "*.xls")
Do while Fname <> ""
.......
0
 
bose3Author Commented:
FYI - Excel didn't recognize file type as "txt" or ".txt". i have to change it to "Text Document".

Thanks for the quick help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now