fso.DeleteFolder works first time, not second

**Chinchin** · 07-01-2011, 11:49 AM

Hi,

I'm pulling my hair out with this problem and would appreciate some help. I'm pretty sure that the first time I run my program it is keeping the folder or file in use somehow so the second time I run it the deletefolder command won't work.

Unfortunately the code is pretty lengthy and hard to follow so I'll just try and describe it for now:

If the folder exists I delete it with fso.DeleteFolder then recreate it and make a few files in it. I use fso objects and also set an object for each file but I've tried setting them to nothing at the end with:

Set RepSheet = Nothing
Set fso = Nothing

The files/folder is not open when I run the second time but still it won't work so I must be missing something. Any advice gratefully received.

Chinchin.

**Leith Ross** · 07-01-2011, 11:57 AM

Hello Chinchin,

After you delete the file or folder assigned to an object variable, you must reassign the object variable to the new instance. If I have a object variable MyFolder that is set to "C:\Test" and I delete it and then recreate the folder "C:\Test", I must reassign MyFolder like this...

   Set MyFolder = "C:\Test"
   FSO.DeleteFolder MyFolder
   MkDir "C:\Test"
   Set MyFolder = "C:\Test"

**Chinchin** · 07-01-2011, 12:20 PM

Thanks Leith, but I don't think that's the problem because I am just using a string rather than an object for the filepath. This is the procedure I'm calling to delete the folder:

Sub DeleteFolder(fol As String)
On Error Resume Next
Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FolderExists(fol) Then fso.DeleteFolder fol, force:=True
Set fso = Nothing
End Sub

Is there something else it could be?

Thanks again,

Chinchin

**Leith Ross** · 07-01-2011, 12:44 PM

Hello Chinchin,

Do you check for the existence of the folder before calling DeleteFolder? It is difficult to asses the problem with so little information.

**Chinchin** · 07-01-2011, 12:52 PM

Hi Leith,

Yes - the problem is actually when I try to recreate the folder it gives error 58 (file already exists) and, when I check, the folder does still exist but with the files gone.

Here's the portion of the code with some bits stripped out:

Case "Reports"
   ' Exit Sub
    Dim FormattedDate As String
    Dim strDir As String
    Dim fs
    Dim RepSheet As Object
    AIMsheet.Activate 'this is set earlier, as is some of the variables below
    FormattedDate = VBA.Strings.Format(Range("EC1").Value, "dd") & " " & VBA.Strings.Format(Range("EC1").Value, "mmm") & " " & VBA.Strings.Format(Range("EC1").Value, "yyyy")

    
    WhichReportForm.Show
    If WhichReportForm.SEbutton Then
        Call DeleteFolder("S:\Horsforth Centre\Customer Liaison shared  area\Paul Maxfield\LCC\LCC Apprenticeships\Pay Reports\SE Pay reports " & FormattedDate)
        
        Set fs = CreateObject("Scripting.FileSystemObject")
        fs.CreateFolder ("S:\Horsforth Centre\Customer Liaison shared  area\Paul Maxfield\LCC\LCC Apprenticeships\Pay Reports\SE Pay reports " & FormattedDate)

        For Each assessor In Range(ASScol & "4:" & ASScol & "6") 'xlLastRow)
            strDir = "S:\Horsforth Centre\Customer Liaison shared  area\Paul Maxfield\LCC\LCC Apprenticeships\Pay Reports\SE Pay reports " & FormattedDate & "\" & assessor.Value & " - " & FormattedDate & ".xlsx"
            If Not FileThere(strDir) And (Range(ASSCONcol & assessor.Row) = "Self-Employed" Or Range(ASSCONcol & assessor.Row) = "Protocol") Then
                Application.SheetsInNewWorkbook = 1
                Workbooks.Add.SaveAs strDir
                Sheets(1).Name = "Payment Breakdown"
                Set RepSheet = Workbooks(assessor.Value & " - " & FormattedDate & ".xlsx").Sheets("Payment Breakdown")
                'make report
            End If
        Next assessor
    End If

    Set RepSheet = Nothing
    Set fs = Nothing
    
End Select

**freekrill** · 07-05-2011, 10:02 PM

I have had this problem too, and have found what's probably an inefficient solution, but all sorts of searches on the net for suggested solutions didn't work for me either.

Like you, I got the error message permission denied the second time I tried to use DeleteFolder command. If I ran the macro again, but this time skipping the first DeleteFolder line, it worked without a problem.

I thought I'd try to call a new macro after the first DeleteFolder, which would effectively do the work of the second DeleteFolder line, but got the same error message.

So, also in frustration, I then tried this which worked. At the end of the macro, I inserted some code to send it back and re-run the macro once more, this time bypassing now irrelevant bits of code and the first DeleteFolder line. For some reason, this works.

Hope this helps
Cheers
free