循环浏览文件扩展名,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屋!