CODEWITHSUNDEEP
vbaexcel
Myles Collier
Myles Collier

Reputation: 23

VBA Verify File extension as excel file?

I run this vba which goes through folders and pulls data which it compiles together in one big sheet. My issue is I was getting errors for hidden files called thumbs.db and I need to add something so that it verifies that it is only pulling files with xlsx extensions. Below is the code I am using.

Sub DoFolder(Folder)
    Dim SubFolder As Folder
    Dim i As Integer
    Dim CopyR As Range

    For Each SubFolder In Folder.SubFolders
        DoFolder SubFolder
    Next

    If Folder.SubFolders.Count = 0 Then
        If Folder.Files.Count = 1 Then
            If Mid(Folder.Files, Len(Folder.Files) - 3, 4) = "xlsx" Then
            Else: MsgBox "2+ files: " & Folder.Path
            End If
        End If

        For Each File In Folder.Files
            Hoover File
        Next
    Else
    End If
End Sub

The line I am having issues with figuring out is 

If Mid(Folder.Files, Len(Folder.Files) - 3, 4) = "xlsx" Then

Any help on this would be really appreciated

Upvotes: 0

Views: 11528

Answers (4)

Andy F
Andy F

Reputation: 91

As a further improvement to the answer by user6432984.. FSO does have a function to obtain the file extension, but the function is not part of the File object, but is the fso.GetExtensionName()

You would expect that the File.Type property could be used, but that gives the application name associated with that file extension - not very useful.

If f.Type Like "*xls*" Then cFiles.Add f

However the FSO-based function works as follows:

For Each f In RootFolder.Files
    If fso.GetExtensionName(f.Path) Like "*xls*" Then cFiles.Add f
Next

Upvotes: 1

user6432984
user6432984

Reputation:

Folder.Files is a collection not a string.

Recursive File Search:

Sub DoFolder(FolderName As String, Optional fso As Object)
    Dim f As Object, MySubFolder As Object, RootFolder As Object
    Dim cFiles As Collection

    If fso Is Nothing Then Set fso = CreateObject("Scripting.FileSystemObject")

    Set RootFolder = fso.GetFolder(FolderName)
    For Each MySubFolder In RootFolder.SubFolders
        DoFolder MySubFolder.Path, fso
    Next

    Set cFiles = New Collection
    For Each f In RootFolder.Files
        If f.Name Like "*xls*" Then cFiles.Add f
    Next

    If cFiles.Count > 0 Then
        MsgBox cFiles.Count & " files found in " & RootFolder.Name
        For Each f In cFiles
            Hoover f
        Next
    End If
End Sub

Upvotes: 5

Jiminy Cricket
Jiminy Cricket

Reputation: 1377

Assuming you're using the FileSystemObject, which it looks like you are even though we can't see the declarations, and assuming you're only wanting to call Hoover for .xlsx files you can use the following code

If Right(File.Name, 4) = "xlsx" Then
    Hoover File
End If

Upvotes: 1

Vityata
Vityata

Reputation: 43585

A quick solution is simply to check for xlsx being contained in the name of the file. Like this:

If InStr(1,"FileName","xlsx",vbTextCompare)<1 then

Thus, you would be in the safe side, unless someone renames thumbs.db to thumbsxlsx.db.

Upvotes: 1

Related Questions