循环遍历文件扩展名，excel vba

我正在使用文件扩展名数组并循环访问工作簿文件夹。该代码正在命名 Sheet(1).name="MyName"

我注意到尽管"*.xlsm"不在数组中，它仍在打开并命名工作表。

这是代码。任何人都可以看看他们是否遇到同样的问题并且能够解决它。

Sub LoopThroughFolder()

    Dim MyFile As String, Str As String, MyDir As String, Wb As Workbook
    Dim Rws As Long, Rng As Range
    Dim fExt, ext
    Set Wb = ThisWorkbook
    'change the address to suite
    MyDir = "C:\TestWorkBookLoop\"
    ChDir MyDir
    Application.ScreenUpdating = 0
    Application.DisplayAlerts = 0
    fExt = Array("*.xlsx", "*.xls")    'file extensions, set the file extensions of the files to move

    For Each ext In fExt    'loop through file extensions
        MyFile = Dir(MyDir & ext)


        Do While MyFile <> ""
            Workbooks.Open (MyFile)
            Sheets(1).Name = "MySheet"

            With ActiveWorkbook
                .Save
                .Close
            End With

            MyFile = Dir()
        Loop
    Next ext
End Sub

旧版短 (8.3) 文件名HELLO.ABCD看起来像ABCDEF~1.ABC- 查看扩展名被截断为 3 个字符。

在你的情况下GET.XLSM将会ABCDEF~1.XLS和这个8.3 表格也匹配 https://blogs.msdn.microsoft.com/oldnewthing/20050720-16/?p=34883通过 Win32 APIFindFirstFile（这是什么Dir()当您指定时在幕后调用）*.XLS

只需过滤掉循环中的异常即可

If Not UCase$(MyFile) Like "*.XLSM" Then 
    ....