Remove folder using RMDIR

[jimmy1981]

Hi,

I'm have a macro that creates a folder with the date as the name, then runs all my queries and places the results in this folder.

What i'm trying to do is to check when the macro is run if the folder already exists, if it does then delete the folder and run the macro again.

Problem i'm having is it wont delete the file because it says it is in use, how do i stop it from being in use?

[FONT=Calibri][FONT=Calibri]dirname = "k:\daily exports\Export Tue-Fri AM\" & Format(Date - 1, "dd-mm-yyyy") & "\"[/FONT]
 
 
[FONT=Calibri]If Dir(dirname, vbDirectory) = "" Then[/FONT]
 
[FONT=Calibri]MkDir dirname 'create the date folder in the exports folder[/FONT]
 
 
[FONT=Calibri]   DoCmd.OutputTo acOutputQuery, "Cleaning B", acFormatXLS, dirname & "Cleaning B " & Format(Date - 1, "dd-mm-yyyy") & ".xls", False, ""[/FONT]
[FONT=Calibri]   DoCmd.OutputTo acOutputQuery, "Cleaning C", acFormatXLS, dirname & "Cleaning C " & Format(Date - 1, "dd-mm-yyyy") & ".xls", False, ""[/FONT]
 
[FONT=Calibri]MsgBox ("Reports have now been created and saved in - " & dirname), vbInformation, "Reports Saved"[/FONT]
 
 
[FONT=Calibri]Else[/FONT]
 
[FONT=Calibri]'MsgBox "The folder already exists..." & Chr(10) & "Please check the directories using Windows Explorer.", vbOKOnly[/FONT]
[FONT=Calibri]RmDir ("k:\daily exports\Export Tue-Fri AM\" & Format(Date - 1, "dd-mm-yyyy"))[/FONT]
[/FONT]

 

[jdraw]

I have a couple of functions that I created and use to
a) make a directory
b) remove a directory

You may be able to adapt them to your situation.

'---------------------------------------------------------------------------------------
' Procedure : MakeDir
' DateTime  : 2005-06-28 10:20
' Author    : drawbrij
' Purpose   : To make a Directory from within VBA. It checks if
'             the Directory to be created already exists, and gives
'             an Error message if so.
'
'Parameters:
'sDrive  - the Drive on which the new directory is to be built
'sDir    - the new directoryName
'
'Note:   - Only creates 1 level per call
'        - the sDir must have leading \ for each level of directory
' eg  MakeDir "C" ,"\level1"         <--call 1
'     MakeDir "C" ,"\level1\level2"  <--call 2
'     will create c:\level1\level2  <--2 Calls required
'---------------------------------------------------------------------------------------
'
Sub MakeDir(sDrive As String, sDir As String)

On Error GoTo ErrorFound
VBA.FileSystem.MkDir sDrive & ":" & sDir
ErrorFound:
If Err.Number = 75 Then
 MsgBox "Err 75 - Directory (" & sDrive & ":" & sDir & ") already exists"
Else
MsgBox Err.Number & " other error " & Err.Description
End If

End Sub
'---------------------------------------------------------------------------------------
' Procedure : RemoveDir
' DateTime  : 2005-06-28 10:29
' Author    : drawbrij
' Purpose   : to remove a directory from within VBA.It checks if
'             the Directory to be removed does not exist, and gives
'             an Error message if so.
'
'Parameters:
'sDrive  - the Drive from which the directory is to be removed
'sDir    - the  directoryName
'
'Note:   - the sDir must have leading \ for each level of directory
' eg  RemoveDir "C" ,"\level1\level2"
'     will remove \level2
' and will leave C:\level1
'---------------------------------------------------------------------------------------
'
Sub RemoveDir(sDrive As String, sDir As String)

On Error GoTo ErrorFound
VBA.FileSystem.RmDir sDrive & ":" & sDir
ErrorFound:
If Err.Number = 76 Then
 MsgBox "Err 76 - Directory (" & sDrive & ":" & sDir & ")  doesn't exist"
End If

End Sub