Release excels hook on a folder.

Posted on 2011-04-27
Last Modified: 2012-05-11
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..."

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

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

End Function

Open in new window

Question by:sirplus
    LVL 81

    Accepted Solution

    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:\"

    LVL 5

    Author Comment

    Its sufficient to just change the directory back & repeat the line
    ChDir StartDirectory
    LVL 81

    Expert Comment

    by:zorvek (Kevin Jones)
    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.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
    This script will sweep a range of IP addresses (class c only, and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
    This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now