Can't Delete Folder after creating it through VBA (1 Viewer)

Oreynolds

Member
Local time
Today, 14:08
Joined
Apr 11, 2020
Messages
157
I have an IF statement that checks to see if a folder exists. If False it creates a folder and copies a specific file into it.

After the above process you can delete the folder through explorer as any normal file operation.

If the above IF statement is True (Folder already exists) then the code runs and copies the same file across overwriting if it is already there which again is what I want.

However, after the TRUE side of the IF statement you cannot delete the folder after it has run......? It's as if there is still some code running but if you go into VBA there is no 'running' in the title bar.

That said the only way I have found you can delete the folder/file is to press RESET in the VBA editor even though there's none running and it doesn't seem to change anything VBA status side. Bar the folder creation the code is identical both sides of the IF statement.

Anyone else had similar troubles?
 

cheekybuddha

AWF VIP
Local time
Today, 14:08
Joined
Jul 21, 2014
Messages
2,237
Please post your code. It's difficult to diagnose without it!
 

Oreynolds

Member
Local time
Today, 14:08
Joined
Apr 11, 2020
Messages
157
Hi sorry to sound thick but am new to forum and have been told to post code in a specific way which I am still not clear on.

Do I just put a:

<

followed by pasting code direct form VBA editor then followed by a:

>

?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:08
Joined
May 7, 2009
Messages
19,169
you need to delete the Files inside the Folder first, then you can Remove the folder.
 

cheekybuddha

AWF VIP
Local time
Today, 14:08
Joined
Jul 21, 2014
Messages
2,237
No problem!

1. copy your code from the vba editor
2. Click the button with the dots with a little dropdown triangle to the right on the post editor toolbar.
3. Select '</> code'
4. A box will pop up. Paste your code in to it.
5. Click 'Continue'.
 

Oreynolds

Member
Local time
Today, 14:08
Joined
Apr 11, 2020
Messages
157
Code:
Private Sub cmdCreateDatasheetsFolder_Click()

'On Error GoTo ErrorHandler

Dim strSQL As String
Dim fsObject As Object
Dim rs As DAO.Recordset
Dim NewPath As String
Dim Cancel As Integer
Dim intStyle As String
Dim strTitle As String
Dim strMsg As String
Dim Foldername As String
Dim FoldernameDest As String
Dim createpath As String
Dim X, I As Integer

'Set folderpath for destination of datasheet files
FoldernameDest = "F:\SFA 20" & Right(Me.OrderDate, 2) & "\Running projects" & "\" & Me.CustomerID.Column(1) & "\J" & Me.OrderID & " " & Me.SiteName.Column(1) & "\OM Manual\Datasheets\"

'Check to see if above directory has been previously created
If FolderExists(FoldernameDest) = False Then
  
    'If it hasnt create the folder
    Foldername = "F:\SFA 20" & Right(Me.OrderDate, 2) & "\Running projects"
    createpath = Foldername & "\" & Me.CustomerID.Column(1) & "\J" & Me.OrderID & " " & Me.SiteName.Column(1) & "\OM Manual\Datasheets\"

    Call MakeDirectory(createpath)

    'MkDir (createpath)

    Foldername = createpath

    'run query to find all products on all quotes linked to job and return their respective datasheet URLs
    strSQL = " SELECT [Quote Details].QuoteID, [Quote Details].ProductID, Products.DatasheetPath, Quotations.OrderNumber " & _
    " FROM ([Quote Details] LEFT JOIN Products ON [Quote Details].ProductID = Products.ProductID) LEFT JOIN Quotations ON [Quote Details].QuoteID = Quotations.QuoteID " & _
    " GROUP BY [Quote Details].QuoteID, [Quote Details].ProductID, Products.DatasheetPath, Quotations.OrderNumber " & _
    " HAVING (((Quotations.OrderNumber)='" & Me.txtOrderNumber & "'));"

    'Set the recordset and then loop through each product in returned recordset and copy each file at a time
    Set rs = CurrentDb.OpenRecordset(strSQL)
    Set fsObject = CreateObject("Scripting.FileSystemObject")

    NewPath = FoldernameDest
    'NewPath = "c:\tempfolderB\"

    With rs
    
        If Not .BOF And Not .EOF Then
        
            .MoveLast
            .MoveFirst
        
            While (Not .EOF)
            
            'rs.Edit
            'rs!DatasheetPath = "2"
            'rs.Update
            
            If IsNull(rs!DatasheetPath) Or rs!DatasheetPath = "" Then
            .MoveNext
            Else
            fsObject.CopyFile rs!DatasheetPath, NewPath
            .MoveNext
            End If
            '.MoveNext

            Wend
      
            Else
            '.Close
            'Set rs = Nothing
            'Set fsObject = Nothing
            MsgBox ("There are no quotes or products linked to this job")
            GoTo ExitSub
      
        End If
    
    .Close

    End With

    Shell "C:\WINDOWS\explorer.exe """ & FoldernameDest & "", vbNormalFocus
    MsgBox ("All datasheets copied to projects folder")

Else

'run query to find all products on all quotes linked to job and return their respective datasheet URLs
    strSQL = " SELECT [Quote Details].QuoteID, [Quote Details].ProductID, Products.DatasheetPath, Quotations.OrderNumber " & _
    " FROM ([Quote Details] LEFT JOIN Products ON [Quote Details].ProductID = Products.ProductID) LEFT JOIN Quotations ON [Quote Details].QuoteID = Quotations.QuoteID " & _
    " GROUP BY [Quote Details].QuoteID, [Quote Details].ProductID, Products.DatasheetPath, Quotations.OrderNumber " & _
    " HAVING (((Quotations.OrderNumber)='" & Me.txtOrderNumber & "'));"

    'Set the recordset and then loop through each product in returned recordset and copy each file at a time
    Set rs = CurrentDb.OpenRecordset(strSQL)
    Set fsObject = CreateObject("Scripting.FileSystemObject")

    NewPath = FoldernameDest
    'NewPath = "c:\tempfolderB\"

    With rs
    
        If Not .BOF And Not .EOF Then
        
            .MoveLast
            .MoveFirst
        
            While (Not .EOF)
            
            'rs.Edit
            'rs!DatasheetPath = "2"
            'rs.Update
            
            If IsNull(rs!DatasheetPath) Or rs!DatasheetPath = "" Then
            .MoveNext
            Else
            fsObject.CopyFile rs!DatasheetPath, NewPath
            .MoveNext
            End If
            '.MoveNext

            Wend
      
            Else
            '.Close
            'Set rs = Nothing
            'Set fsObject = Nothing
            MsgBox ("There are no quotes or products linked to this job")
            GoTo ExitSub
      
        End If
    
    .Close

    End With

    Shell "C:\WINDOWS\explorer.exe """ & FoldernameDest & "", vbNormalFocus
    MsgBox ("All datasheets copied to projects folder GGGGGGGGGGGGGGG")

End If

GoTo ExitSub

ExitSub:
    Set rs = Nothing
    Set fsObject = Nothing
    
    Exit Sub
ErrorHandler:
MsgBox "None of the linked products had datasheets assigned"
    Resume ExitSub
End Sub
 

Oreynolds

Member
Local time
Today, 14:08
Joined
Apr 11, 2020
Messages
157
No problem!

1. copy your code from the vba editor
2. Click the button with the dots with a little dropdown triangle to the right on the post editor toolbar.
3. Select '</> code'
4. A box will pop up. Paste your code in to it.
5. Click 'Continue'.
]
Thanks so much, totally understand now! Appreciated
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:08
Joined
May 7, 2009
Messages
19,169
hmm, so you are using FSO (FilesystemObject)?

'get the Folder Object
Dim objFolder As Object
Set objFolder = objFSO.GetFolder("the path here")
objFolder.Delete
Set objFolder = Nothing

edit: Now, where is your code for deleting/Removing the folder?
 

Oreynolds

Member
Local time
Today, 14:08
Joined
Apr 11, 2020
Messages
157
hmm, so you are using FSO (FilesystemObject)?

'get the Folder Object
Dim objFolder As Object
Set objFolder = objFSO.GetFolder("the path here")
objFolder.Delete
Set objFolder = Nothing

Thanks.

I may not have made myself clear. I am not trying to delete the folder using VBA. The problem occurs when the user runs the code to create the folder and then subsequently uses file explorer to try and delete is as its no longer wanted they can't and get a windows error.

I can simulate this and the only way I can get the folder to release and subsequently delete is to press the RESET button in the VBA editor, you can then easily delete it through file explorer
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:08
Joined
May 7, 2009
Messages
19,169
it maybe that FSO has a hold on it, so it is always best to

Set
fsObject = Nothing

after copying/deleting the files, only then you can "Shell.." to that folder.

you have not shown yet the MakeDirectory() function
 

Oreynolds

Member
Local time
Today, 14:08
Joined
Apr 11, 2020
Messages
157
it maybe that FSO has a hold on it, so it is always best to

Set
fsObject = Nothing

after copying/deleting the files, only then you can "Shell.." to that folder.

you have not shown yet the MakeDirectory() function

Code:
'routine to create your directory path
Public Sub MakeDirectory(folderpath As String)
Dim X, I As Integer, strPath As String
X = Split(folderpath, "\")

For I = 0 To UBound(X) - 1
    strPath = strPath & X(I) & "\"
    If Not FolderExists(strPath) Then MkDir strPath
Next I

End Sub
 

Oreynolds

Member
Local time
Today, 14:08
Joined
Apr 11, 2020
Messages
157
Code:
'routine to create your directory path
Public Sub MakeDirectory(folderpath As String)
Dim X, I As Integer, strPath As String
X = Split(folderpath, "\")

For I = 0 To UBound(X) - 1
    strPath = strPath & X(I) & "\"
    If Not FolderExists(strPath) Then MkDir strPath
Next I

End Sub

Code:
'function to check if folder exist
Function FolderExists(folderpath As String) As Boolean
On Error Resume Next

ChDir folderpath
If Err Then FolderExists = False Else FolderExists = True

End Function
 

Oreynolds

Member
Local time
Today, 14:08
Joined
Apr 11, 2020
Messages
157
What I can't understand is that the resulting code after the directory is checked is the same, yet the FALSE side of the IF statement you can delete the folder and yet the TRUE side you cannot unless you press the VBA RESET button......?
 

Oreynolds

Member
Local time
Today, 14:08
Joined
Apr 11, 2020
Messages
157
it maybe that FSO has a hold on it, so it is always best to

Set
fsObject = Nothing

after copying/deleting the files, only then you can "Shell.." to that folder.

you have not shown yet the MakeDirectory() function

Hi I tried your suggestion as below but still has no effect on allowing Directory to be deleted....?

Code:
        End If
    
    .Close

    End With

    Set fsObject = Nothing
    Shell "C:\WINDOWS\explorer.exe """ & FoldernameDest & "", vbNormalFocus
    MsgBox ("All datasheets copied to projects folder GGGGGGGGGGGGGGG")

End If

GoTo ExitSub
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:08
Joined
May 7, 2009
Messages
19,169
are you sure the folder is empty?
there maybe hidden files (you can use Attrib *.* to list all file attributes), still on that folder.
 

Oreynolds

Member
Local time
Today, 14:08
Joined
Apr 11, 2020
Messages
157
are you sure the folder is empty?
there maybe hidden files (you can use Attrib *.* to list all file attributes), still on that folder.

No, I have checked that. You just can't delete it until you press the VBA reset button, then it deletes as normal!
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:08
Joined
May 7, 2009
Messages
19,169
can you try, Application.FollowHyperlink instead of shell.
 

Oreynolds

Member
Local time
Today, 14:08
Joined
Apr 11, 2020
Messages
157
Sorry, I've not used application.followhyperlink before, tried this but get code error:

Application.FollowHyperlink "C:\WINDOWS\explorer.exe """ & FoldernameDest & "", vbNormalFocus
 

Users who are viewing this thread

Top Bottom