Working With Files in VBA Macros

[coasterfreak]

I am trying to find out if there is a way to automatically find out if there are files in a directory and delete them so I can remove the directory. I want to do all this through a macro in VBA. Another possibility is to error check and if the error happends then do something else but I cannot figure out the If Statement for an error code 75 or 76. My final program will be a startup program on a disk so that the user can run the excel program by choosing what Excel programs they want on their computer and the program will make the directories and load programs in those files. I can make the directories and move the files but my problem is that if the directory already exists then aI get an error. I know this is a lot to check for not much of a good reason but I try to check for everything. I will be thankful for any help.

 

[Weaver]

Have you tried googling "filesystemobject"?

 

[bk]

This is the gist of what you need:

With Application.FileSearch
.NewSearch
.LookIn = "my path and folder"
If .Execute() > 0 Then
For variable = 1 to .FoundFiles.Count
my action(s)
Next i
End If
End With


Some comments:
1- I dont' do this much; so, I'd have to find the exact code to see the delete command
2- Off top of my head, I don't remember if this is only good for the Excel files in the directory or all files.
3- I found the code I'll look for on this forum years ago. So, if you search something like "delete files directory" or something you should get some responses. And that may be quicker than my gettting back.

 

[iliace]

Using VBA's built-in functions.

Public Sub fileSearch()
  Dim myDir As String
  Dim myFile As String
  
  myDir = "c:\temp"
  myFile = "*.*"
  
  If VBA.Right$(myDir, 1) <> Application.PathSeparator Then
    myDir = myDir & "\"
  End If
  
  Dim nextFile As String
  
  nextFile = VBA.Dir(myDir & myFile)
  Do Until VBA.Len(nextFile) = 0
    VBA.Kill myDir & nextFile
    nextFile = VBA.Dir()
  Loop
End Sub

I added this part in case you want to pass the folder name as a parameter:

  If VBA.Right$(myDir, 1) <> Application.PathSeparator Then
    myDir = myDir & "\"
  End If

 

[coasterfreak]

I tried the .FileSearch and it does not work for some reason gives me a run-time error 445 -- Object doesn't support this action (Error 445)

 

[coasterfreak]

Thanks so Much for your help iliace!!! That works my files were gone. I did find it interesting that it did not ask if i was sure I want to delete the files cause I did not add the line " Application.DisplayAlerts = False" to my sub. Now I have to put this thing back together. i had tryed so much that I had chopped it up. I now know where to go for help. Thanks again!!!

 

[iliace]

Yes, VBA will brutally delete with no warning. You could send them to the recycling bin, but that's considerably more work. This here is adapted from Bullen/Bovey/Green, as many of my examples are - thank god for those guys:

Private Const FO_DELETE = &H3
Private Const FOF_SILENT = &H4
Private Const FOF_NOCONFIRMATION = &H10
Private Const FOF_ALLOWUNDO = &H40
Private Declare Function SHFileOperation _
        Lib "shell32.dll" Alias "SHFileOperationA" _
            (ByRef lpFileOp As SHFILEOPSTRUCT) As Long
Private Type SHFILEOPSTRUCT
  hWnd As Long
  wFunc As Long
  pFrom As String
  pTo As String
  fFlags As Integer
  fAnyOperationsAborted As Boolean
  hNameMappings As Long
  lpszProgressTitle As String
End Type
 
Sub RecycleMe(ByVal sFile As String, progress As Boolean, ask As Boolean, undo As Boolean)
  Dim oper As SHFILEOPSTRUCT
  Dim l As Long
  With oper
    .wFunc = FO_DELETE
    .pFrom = sFile
    .pTo = vbNullChar
    If Not progress Then .fFlags = FOF_SILENT
    If Not ask Then .fFlags = .fFlags + FOF_NOCONFIRMATION
    If Not undo Then .fFlags = .fFlags + FOF_ALLOWUNDO
  End With
  l = SHFileOperation(oper)
End Sub
 
Sub deleteFiles()
  Dim myDir As String
  Dim myFile As String
 
  myDir = "c:\temp"
  myFile = "*.*"
 
  If VBA.Right$(myDir, 1) <> Application.PathSeparator Then
    myDir = myDir & "\"
  End If
 
  Dim nextFile As String
 
  nextFile = VBA.Dir(myDir & myFile)
  Do Until VBA.Len(nextFile) = 0
    RecycleMe myDir & nextFile, True, True, True
    nextFile = VBA.Dir()
  Loop
End Sub

You run deleteFiles as before. RecycleMe takes four parameters: path and file name, true if you want to display progress (false if not), true if you want to confirm (yes/no), and true if you want to allow undo from a Windows Explorer window. In all cases files are sent to the recycling bin. Try it out.

 

[coasterfreak]

Thanks, I tried it but kept giving me probs. I will simply write another userform to advise the user he is about to delete all files and give him the choice to continue. I really get into userforms and adding all possible errors caused by the user which sometimes is extensive.