循环浏览文件扩展名，Excel VBA [英] Looping through file extensions, excel vba

问题描述

我正在使用文件扩展名数组，并在工作簿的文件夹中循环.该代码将工作表命名为Sheet(1).name ="MyName"

I am using an Array of File extensions and looping through a folder of workbooks. The code is naming Sheet(1).name="MyName"

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

I notice that even though "*.xlsm" is not in the array, it is still opening and naming the sheet.

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

Here's the code. Can anybody see if they get the same problem and are able to solve it.

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

推荐答案

HELLO.ABCD 的旧式短(8.3)文件名看起来类似于 ABCDEF〜1.ABC -看到扩展名被截断为3个字符.

The legacy short (8.3) file name for HELLO.ABCD would look something like ABCDEF~1.ABC - see the extension is truncated to 3 characters.

在您的情况下， GET.XLSM 将为 ABCDEF〜1.XLS ，并且此8.3格式也与Win32 API FindFirstFile (这是 Dir()调用的内容)相匹配)，当您指定 *.XLS

In your case GET.XLSM would be ABCDEF~1.XLS and this 8.3 form is also matched by the Win32 API FindFirstFile (which is what Dir() calls under the hood) when you specify *.XLS

只需用

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

这篇关于循环浏览文件扩展名，Excel VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！