Excel vba只删除指定的扩展

I trying to kill all files with extension xls

我试图用扩展名xls杀死所有文件

Sub testt()
downloadF = Environ("USERPROFILE") & "\Downloads\*.xls"

Kill downloadF

End Sub

But it also kill files .xlsx and .xlsm and everything with .xls*

但它也会删除.xlsx和.xlsm以及所有带有.xls*的文件

Why?

为什么?

How to kill only *.xls?

如何只杀*.xls?

4 个解决方案

#1

I have a theory as to why this is happening, but I haven't quite proven it... in the meantime I found an alternate method to delete only the intended files is to refer to the file's "short" (8.3) name:

我有一个理论解释为什么会发生这种情况，但我还没有完全证明……在此期间，我发现了一个替代的方法，只删除预期的文件，以引用文件的“short”(8.3)名称:

For example, when I first checked my (NTFS) drive, using the /X switch with Dir at the command prompt:

例如，当我第一次检查我的(NTFS)驱动器时，在命令提示符下使用带有Dir的/X开关:

      t.xlsx has a short name of TF99B~1.XLS

t。xlsx有一个简称TF99B~1.XLS

...and with Dir /x:

…和Dir / x:

...and programmatically:

…和编程:

Option Explicit

Private Declare Function GetShortPathNameA Lib "kernel32" _
(ByVal lpszLongPath As String, ByVal lpszShortPath As String, ByVal cchBuffer As Long) As Long

Public Function ShortPath(ByVal fName As String) As String
    Dim fNum As Integer, strBuffer As String * 255
    fNum = FreeFile
    If Dir(fName) = "" Then
        On Error Resume Next 'Create file if it doesn't exist
        Open fName For Output As #fNum
        Close #fNum
    End If
    ShortPath = Left$(strBuffer, GetShortPathNameA(fName, strBuffer, 255))
End Function

As @Pᴇʜ pointed out, if you strip these from the files, eg. with:

作为@Pᴇʜ指出的那样,如果你带这些文件,例如。:

fsutil 8dot3name strip c:\temp\test   

...the Kill command works as expected (and does not kill xlsx).

…Kill命令按预期工作(并且不杀死xlsx)。

fsutil 8dot3name strip : Removes the 8dot3 file names for all files that are located in the specified DirectoryPath. The 8dot3 file name is not removed for any files where the DirectoryPath combined with the file name contains more than 260 characters.

fsutil 8dot3name带:删除位于指定的DirectoryPath中的所有文件的8dot3文件名。如果DirectoryPath结合文件名包含超过260个字符，则不会删除8dot3文件名。

This command lists, but does not modify the registry keys that point to the files that had 8dot3 file names permanently removed.

此命令列出，但不修改指向永久删除了8dot3文件名的文件的注册表键。

For more information about the effects of permanently removing the 8dot3 file names from files, see Remarks.

有关永久删除文件的8dot3文件名称的影响的更多信息，请参见备注。

---

...and through the command line, for a whole folder or volume at once:

…并通过命令行，对整个文件夹或卷同时:

To query for the disable 8dot3 name behavior for a disk volume that is for a specific volume, use:

要查询针对特定卷的磁盘卷的禁用8dot3名称行为，请使用:

fsutil 8dot3name query Volume{xyz-VolumeGUID-xyz}

You can also query the 8dot3 name behavior by using the behavior subcommand.

您还可以使用行为子命令查询8dot3名称行为。

To remove 8dot3 file names in the D:\MyData directory and all subdirectories, while writing the information to the log file that is specified as mylogfile.log, type:

要删除D:\MyData目录和所有子目录中的8dot3文件名，同时将信息写入指定为mylogfile的日志文件。日志,类型:

fsutil 8dot3name scan /l mylogfile.log /s d:\MyData

More Info:

更多信息:

• Fsutil 8dot3name documentation

  Fsutil 8 dot3name文档

• Fsutil documentation

  Fsutil文档

---

Namespaces

'^{Source: Naming Files, Paths, and Namespaces (Microsoft)}

’来源:命名文件、路径和名称空间(微软)

All file systems follow the same general naming conventions for an individual file: a base file name and an optional extension, separated by a period. However, each file system, such as NTFS, CDFS, exFAT, UDFS, FAT, and FAT32, can have specific and differing rules about the formation of the individual components in the path to a directory or file.

所有文件系统都遵循单个文件的相同的通用命名约定:基文件名和可选扩展名，用句号分隔。但是，每个文件系统，如NTFS、CDFS、exFAT、UDFS、FAT和FAT32，都可以有关于在到目录或文件的路径中形成单个组件的特定和不同的规则。

. . .

。

Character count limitations can also be different and can vary depending on the file system and path name prefix format used. This is further complicated by support for backward compatibility mechanisms. For example, the older MS-DOS FAT file system supports a maximum of 8 characters for the base file name and 3 characters for the extension, for a total of 12 characters including the dot separator. This is commonly known as an 8.3 file name. The Windows FAT and NTFS file systems are not limited to 8.3 file names, because they have long file name support, but they still support the 8.3 version of long file names.

字符计数限制也可以不同，根据使用的文件系统和路径名前缀格式而不同。由于支持向后兼容机制，这就更加复杂。例如，旧的MS-DOS FAT文件系统支持最多8个字符的基本文件名和3个字符的扩展，总共12个字符，包括点分隔符。这通常称为8.3文件名。Windows FAT和NTFS文件系统不限于8.3文件名，因为它们支持长文件名，但是它们仍然支持8.3版本的长文件名。

---

\\?\

Win32 File Namespaces

Win32文件名称空间

For file I/O, the \\?\ prefix to a path string tells the Windows APIs to disable all string parsing and to send the string that follows it straight to the file system. For example, if the file system supports large paths and file names, you can exceed the MAX_PATH limits that are otherwise enforced by the Windows APIs. For more information about the normal maximum path limitation, see the section Maximum Path Length Limitation.

用于文件I/O， \?\对路径字符串的前缀告诉Windows api禁用所有字符串解析，并将跟踪它的字符串直接发送到文件系统。例如，如果文件系统支持大路径和文件名，则可以超出Windows api执行的MAX_PATH限制。有关正常最大路径限制的更多信息，请参见区段最大路径长度限制。

Because it turns off automatic expansion of the path string, the \\?\ prefix also allows the use of .. and . in the path names, which can be useful if you are attempting to perform operations on a file with these otherwise reserved relative path specifiers as part of the fully qualified path.

因为它关闭了路径字符串的自动扩展，\?\前缀也允许…和。在路径名中，如果您试图在文件上执行操作，而将这些其他保留的相对路径说明符作为完全限定路径的一部分，这将非常有用。

Many but not all file I/O APIs support \\?\; you should look at the reference topic for each API to be sure.

许多但不是所有的文件I/O api都支持\?\;您应该查看每个API的引用主题以确定。

---

\\.\

Win32 Device Namespaces

Win32设备名称空间

The \\.\ prefix will access the Win32 device namespace instead of the Win32 file namespace. This is how access to physical disks and volumes is accomplished directly, without going through the file system, if the API supports this type of access. You can access many devices other than disks this way (using the CreateFile and DefineDosDevice functions, for example).

\ \。\前缀将访问Win32设备名称空间而不是Win32文件名称空间。如果API支持这种类型的访问，这就是直接访问物理磁盘和卷的方式，而不需要通过文件系统。您可以通过这种方式访问除磁盘之外的许多设备(例如使用CreateFile和DefineDosDevice函数)。

---

NT Namespaces

NT名称空间

There are also APIs that allow the use of the NT namespace convention, but the Windows Object Manager makes that unnecessary in most cases. To illustrate, it is useful to browse the Windows namespaces in the system object browser using the Windows Sysinternals WinObj tool. When you run this tool, what you see is the NT namespace beginning at the root, or \. The subfolder called Global?? is where the Win32 namespace resides.

还有一些api允许使用NT名称空间约定，但是Windows对象管理器在大多数情况下都是不必要的。举例来说，使用Windows Sysinternals WinObj工具在系统对象浏览器中浏览Windows名称空间是很有用的。当您运行这个工具时，您看到的是NT名称空间从根开始，或者\。子文件夹称为全球?是Win32命名空间所在的位置。

---

FAT Naming Convention

^{Source: Overview of FAT, HPFS, and NTFS File Systems (Microsoft)}

源:FAT、HPFS和NTFS文件系统概述(Microsoft)

FAT uses the traditional 8.3 file naming convention and all filenames must be created with the ASCII character set. The name of a file or directory can be up to eight characters long, then a period . separator, and up to a three character extension. The name must start with either a letter or number and can contain any characters except for the following:

FAT使用传统的8.3文件命名约定，所有文件名必须使用ASCII字符集创建。文件或目录的名称最多可以有8个字符长，然后是一个句号。分隔符，最多可扩展三个字符。名字必须以字母或数字开头，可以包含除以下内容外的任何字符:

. " / \ [ ] : ; | = ,

。“/ \ []:;| =、

If any of these characters are used, unexpected results may occur. The name cannot contain any spaces.

如果使用这些字符中的任何一个，可能会出现意外结果。名称不能包含任何空格。

---

NTFS Naming Conventions

File and directory names can be up to 255 characters long, including any extensions. Names preserve case, but are not case sensitive. NTFS makes no distinction of filenames based on case. Names can contain any characters except for the following:

文件和目录名最长可达255个字符，包括任何扩展。名称保留大小写，但不区分大小写。NTFS不会根据情况区分文件名。除以下内容外，名称可以包含任何字符:

? " / \ < > * | :

吗?" / \ < > * |:

Currently, from the command line, you can only create file names of up to 253 characters.

目前，在命令行中，最多只能创建253个字符的文件名。

NOTE: Underlying hardware limitations may impose additional partition size limitations in any file system. Particularly, a boot partition can be only 7.8 GB in size, and there is a 2-terabyte limitation in the partition table.

注意:底层硬件限制可能会在任何文件系统中强加额外的分区大小限制。特别是，一个引导分区的大小只能是7.8 GB，并且分区表中有2 tb的限制。

---

More Information

• MSDN : DeleteFile function

  MSDN:DeleteFile函数

• MSDN : DeleteFileFromApp function

  MSDN:DeleteFileFromApp函数

• MSDN : CheckNameLegalDOS8Dot3 function

  MSDN:CheckNameLegalDOS8Dot3函数

#2

Try

试一试

Option Explicit
Public Sub DelFiles()
    Dim fso As Object, fol As Object, f As Object

    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fol = fso.GetFolder(Environ$("USERPROFILE") & "\Downloads")

    For Each f In fol.Files
        'Debug.Print f
        If fso.GetExtensionName(f) = "xls" Then Kill f
    Next f
End Sub

#3

Kill Environ("USERPROFILE") & "\Downloads\*.xls" should kill the .xls only. However, can you try the following:

杀死环境(“USERPROFILE”)和“下载\ \ *。xls"应该只杀死。xls。但是，你可以试一下:

1. Open any folder in Windows
2. 在Windows中打开任何文件夹
3. Go to View
4. 查看
5. Options
6. 选项
7. View
8. 视图
9. Uncheck "Hide extensions for known file types"
10. 取消检查“隐藏已知文件类型的扩展名”
11. Click OK
12. 单击OK
13. See what is going on
14. 看看发生了什么

#4

I have no explanation for that, but found something strange. I created 2 files, one test1.xls and with test2.xlsx. I copied these files to various places:

对此我没有任何解释，但发现了一些奇怪的东西。我创建了两个文件，一个test1。xls和test2.xlsx。我把这些文件复制到不同的地方:

• To %userprofile%\downloads
• % userprofile % \下载
• to %userprofile%\documents
• % userprofile % \文档
• to C:\junk
• C:\垃圾
• to U:\junk (where U: is a network share)
• 致U:\垃圾(其中U:为网络共享)
• to G:\MyDrive\MyDocs\junk (where G: is created by Google File Stream)
• to G:\MyDrive MyDocs\ garbage(其中G:由谷歌文件流创建)

For the first three cases (where the files live on C:\), the VBA command dir *.xls and also the Command-prompt dir *.xls listed both files, while for the "foreign" drives, only the test1.xls was listed. I assume that the kill used the same logic.

对于前三种情况(文件位于C:\)， VBA命令dir *。xls和命令提示符dir *。xls列出了这两个文件，而对于“外”驱动器，只有test1。xls上市。我假定杀戮使用了相同的逻辑。

(Tested on Windows 10)

(在Windows上测试10)