VBA验证文件扩展名是excel文件吗? [英] VBA Verify File extension as excel file?

问题描述

我运行此vba，该vba会遍历文件夹并将其编译在一起的数据拉成一张大纸.我的问题是，隐藏的文件thumbs.db出现错误，我需要添加一些内容，以验证它是否仅提取具有xlsx扩展名的文件.下面是我正在使用的代码.

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

我要解决的问题是

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

在此方面的任何帮助将不胜感激

Any help on this would be really appreciated

推荐答案

文件夹.文件是一个集合，而不是字符串.

Folder.Files is a collection not a string.

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

这篇关于VBA验证文件扩展名是excel文件吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！