Excel vba kill only specified extension

Question

I trying to kill all files with extension xls

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

Kill downloadF

End Sub

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

Why?

How to kill only *.xls?

Answer 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:

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

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

...and with 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:

fsutil 8dot3name strip c:\temp\test   

...the Kill command works as expected (and does not 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.

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

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

---

...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:

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

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

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:

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

More Info:

• Fsutil 8dot3name documentation

• Fsutil documentation

---

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.

. . .

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.

---

\\?\

Win32 File Namespaces

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.

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.

---

\\.\

Win32 Device Namespaces

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).

---

NT Namespaces

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.

---

FAT Naming Convention

^{Source: Overview of FAT, HPFS, and NTFS File Systems (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:

. " / \ [ ] : ; | = ,

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:

? " / \ < > * | :

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

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.

---

More Information

• MSDN : DeleteFile function

• MSDN : DeleteFileFromApp function

• MSDN : CheckNameLegalDOS8Dot3 function

Answer 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