• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 329
  • Last Modified:

Release excels hook on a folder.

The code below allows a user to select a file and traps the path to that file as a string.

The file is then edited and moved out of the folder which is then left empty.

I want to then delete the folder so as to clean up properly.

A "permission denied" error occurs whenever deletion of said folder is attempted at any time after execution of the line

    fFullPATH = CStr(Application.GetOpenFilename(FileFilter, FilterIndex, Title, ButtonText, MultiSelect))

The folder can be deleted by excel or directly by the windows operating system e.g. windows explorer etc. at any time prior to this and also at any time after excel application is closed (and restarted -as required if deleting the folder programmatically).

How do I release the hook excel has on the folder containing the file?  
Option Explicit
Const FileExtension_Order = "txt"


Function GetOrderFile() As String
    Const sNameProc = "GetOrderFile" 'Specify the Function Name

    Dim pdf As Variant
    Dim Title As String, fPrefix As String, fFullPATH As String, FileName_New As String, FileName_NewExtended As String 'As String
    Dim PDFType ' as Array'As String
    Dim N As Integer
    Dim StartDirectory As String
    
    Const FilterIndex As Integer = Empty
    Const ButtonText = Empty
    Const MultiSelect As Boolean = False
    Const FileFilter = "Order Files (*." & FileExtension_Order & "),*." & FileExtension_Order & ""
    StartDirectory = "C:\temp"

ChDrive Left(StartDirectory, 1)
ChDir StartDirectory
    
    N = 0
    Title = """Double-Click"" the Folder for your job then choose your file..."

tryagain:
    N = N + 1
    fFullPATH = CStr(Application.GetOpenFilename(FileFilter, FilterIndex, Title, ButtonText, MultiSelect))
If fFullPATH = vbNullString Then
    End
End If

If fFullPATH = "False" Then
    If N < 3 Then
        MsgBox """Double-Click"" the Folder for your job"
        GoTo tryagain
    Else
        MsgBox ""
        Exit Function
    End If
Else
    GetOrderFile = fFullPATH
End If

End Function

Open in new window

0
sir plus
Asked:
sir plus
  • 2
1 Solution
 
zorvek (Kevin Jones)ConsultantCommented:
When a GetSaveAsFilename or GetOpenFilename function is used the last viewed folder is locked. To release the lock use the ChDrive and ChDir commands to change the current directory:

   Dim FileName As String
   FileName = ThisWorkbook.GetSaveAsFilename
   ChDrive = "C"
   ChDir = "C:\"

Kevin
0
 
sir plusSales ManagementAuthor Commented:
Its sufficient to just change the directory back & repeat the line
ChDir StartDirectory
0
 
zorvek (Kevin Jones)ConsultantCommented:
I have found instances on multiple drive systems where a ChDrive is also necessary to unlock the folder.

Also, changing the directory isn't going to do much if that's where the selected file is located.

Kevin
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now