Delete Files in Dir

[McM]

I am stuck. How can I delete files in a dir, but not the file I have open in that dir?

Thank for all the help.

McM

 

[lozzablake]

Try this. You need to have reference set to the Microsoft Scripting Runtime. Obviously test this first!

Sub DeleteFiles()
'macro to delete all files in a folder
    
    Dim FileSys As FileSystemObject
    Dim objFile As File
    Dim myFolder
    Dim strFilename As String
    Dim dteFile As Date
        
    'set path for files - change for your folder
    Const myDir As String = "C:\"
    
    'set up filesys objects
    Set FileSys = New FileSystemObject
    Set myFolder = FileSys.GetFolder(myDir)
        
    
    'loop through each file and check for name match
    For Each objFile In myFolder.Files
        If objFile.Name <> ThisWorkbook.Name Then
            objFile.Delete
            
        End If
    Next objFile
    
            
    Set FileSys = Nothing
    Set myFolder = Nothing
End Sub

 

[McM]

Thanks for responding lozzablake. I will test this and let you know.

Thanks again

Mcm

 

[McM]

I dont think I gave enough information.

Here is the code I already have

Dim strPath As String, strFileMask As String
strPath = "C:\Documents and Settings\All Users\Desktop\"
strFileMask = "*test*.xls"
If Dir(strPath & strFileMask) <> "" Then
Kill strPath & strFileMask
Else
MsgBox "No Files Found "
End If

It error on Kill strPath & strFileMask because I have the file open. Is there a way to delete all the xls files with the word test in the name, except the test file that is open?

Thanks for the help.

 

[lozzablake]

I have changed the code slightly to only delete if it contains test by using the INSTR function

Sub DeleteFiles()
'macro to delete all files in a folder
    
    Dim FileSys As FileSystemObject
    Dim objFile As File
    Dim myFolder
    Dim strFilename As String
    Dim dteFile As Date
        
    'set path for files - change for your folder
    Const myDir As String = "C:\"
    
    'set up filesys objects
    Set FileSys = New FileSystemObject
    Set myFolder = FileSys.GetFolder(myDir)
        
    
    'loop through each file and check for name match on *test*
    For Each objFile In myFolder.Files
        If (InStr(1, UCase(objFile.Name), "TEST")) > 0 And objFile.Name <> ThisWorkbook.Name Then
            objFile.Delete
            
        End If
    Next objFile
    
            
    Set FileSys = Nothing
    Set myFolder = Nothing
End Sub

 

[McM]

I get a compile error on
Dim FileSys As FileSystemObject

I am sure it is becasue I do not know what to do with this:

'set up filesys objects
Set FileSys = New FileSystemObject
Set myFolder = FileSys.GetFolder(myDir)

I do apologize, I am very new at this.

Thanks for your help.

 

[McM]

Any ideas?

 

[maytas]

Hi,

In VBE window select Tools =>References=>Microsoft Scripting Runtime.

 

[McM]

Thank You lozzablake, this does exactly what I needed.

and

Thank You Maytas for showing me what to change.