none
VBA - verify if user has permission to directory before SaveAs attempt RRS feed

  • Question

  • Hello,

    I'm seeing alot of related topics on this, but no definitive answer for VBA in Excel.

    My ultimate goal is to perform a SaveAs of the user's worksheet into .csv format (I have that piece taken care of) - but rather than force a Windows error on the user if they don't have folder access to the destination directory, I'd like to be able to handle the situation automatically.

    Checking beforehand if user has write permissions to the directory is my strategy, but again... the only solution I've seen involves catching the error after the SaveAs attempt.

    Has anyone been able to come up with a proactive solution for this?

    Thanks,
    John

    Thursday, October 3, 2013 1:55 PM

Answers

  • You might try this approach:

    Function IsPathWritable(ByVal FPath As String) As Boolean
        Dim FName As String
        Dim FHdl As Integer
        Dim Counter As Integer
        If (Right(FPath, 1) <> "\") Then FPath = FPath & "\"
        Do
            FName = FPath & "TempFile" & Counter & ".tmp"
        Loop Until Dir(FName) = ""
        On Error GoTo CantWrite
        FHdl = FreeFile()
        Open FName For Output Access Write As FHdl
        Print #FHdl, "TESTWRITE"
        Close FHdl
        IsPathWritable = True
        Kill FName
    CantWrite:
    End Function

    Jim

    Friday, October 4, 2013 1:30 PM

All replies

  • Catching the error (in this case error 75) is the standard way to handle the situation. You provide your own message to the user and the end result can be exactly what you were hoping for with a "proactive" solution.

    FWIW: Handling common situations in a uncommon way will lead to confusion for future developers who inherit your code. There's a reason you've only seen one solution to this problem--it's the right one.

    Thursday, October 3, 2013 6:56 PM
  • Are you referring to an OS/network error number or to VBA?  I'm not getting error 75.  I'm getting error 1004 in VBA which only tells me the SaveAs failed - so it's not explicit that the error is due to lack of directory permissions.  That's why I'm considering how to explicitly test for directory permissions.

    If you're referring to the OS/network error number how are you catching it?

    Thursday, October 3, 2013 8:03 PM
  • When automating Excel, errors generated by Excel will always have the same number: 1004. However, the description is accurate. So you could check to see if the error description is "Path/File Access Error".
    Thursday, October 3, 2013 10:06 PM
  • You might try this approach:

    Function IsPathWritable(ByVal FPath As String) As Boolean
        Dim FName As String
        Dim FHdl As Integer
        Dim Counter As Integer
        If (Right(FPath, 1) <> "\") Then FPath = FPath & "\"
        Do
            FName = FPath & "TempFile" & Counter & ".tmp"
        Loop Until Dir(FName) = ""
        On Error GoTo CantWrite
        FHdl = FreeFile()
        Open FName For Output Access Write As FHdl
        Print #FHdl, "TESTWRITE"
        Close FHdl
        IsPathWritable = True
        Kill FName
    CantWrite:
    End Function

    Jim

    Friday, October 4, 2013 1:30 PM
  • In the end all this code is doing is trapping for a path/file access error. You get the same results by simply trying to save the csv file in the first place.
    Friday, October 4, 2013 5:49 PM
  • @ RachelHettinger

    I disagree with it being the only and right solution.

    We've developed a document control system used by multiple users at one time. Users in different departments are able to modify files only in their department or elsewhere as needed.

    When someone is trained to maintain controlled documents IT provides them with permissions to the QA folder.

    Someone without permission to modify is still able to add a file or folder, then they are unable to delete or modify it.

    It would be very beneficial for me to verify a user's access before getting into building the customized file and realizing you cannot save it.

    If UserHasPermissions(strDirectory) Then
    
        UpdateFiles
    
    Else
    
        Msgbox "Please contact IT for permissions to modify files in the QA folder."
    
    End IF



    • Edited by Hyrumdrums Thursday, February 1, 2018 2:24 PM
    Wednesday, November 22, 2017 5:58 PM
  • Hyrumdrums, I'm not sure I follow your objective nor what in particular you are referring to when you say "it" is not the only solution.

    Have you tried the approach suggested by Jim Rech, it's a quick, simple and reliable way to pre-check if the user has  folder write permission, though might also want to check the folder actually exists which is a different issue.

    The response to Jim's suggestion that it is in effect the same as "trying to save the csv in the first place" is not really correct. There could be other reasons for failure to write the csv, such as a file with that name already exists and perhaps in use.

    Thursday, November 23, 2017 2:46 PM
    Moderator
  • Peter Thornton

    I have tried solutions like Jim's yes. The problem is that there are hundreds of folders that will allow you:

    • to add the file, modify, but not delete it.
    • to add the file but not modify or delete it.

    It's a great function, but really a brute force method that would leave TempFile0.tmp files all over the place in our situation.

    The most ideal solution would not involve trying to write a temporary file.

    Thursday, February 1, 2018 2:54 PM
  • no expert by any means but it seems that, as suggested controlling who can change a file is the solution.

    in an application I developed (with a lot of guidance from people on this forum) I need everyone to view files for information purposes and I need some to be able to edit the files. available files list in a combobox and I have 2 buttons: one to simply view file data. the other to edit which requires a password - works perfectly for us!!

    essentially in view mode it opens a file, copies data to a temporary file and closes the original leaving it available to others-there is no mechanism to save the file while viewing.

    in edit mode it opens a file, copies data to a temporary file, adds 'file in use' to the original and then closes.

    when someone tries to click the edit button it checks to see if 'file in use' exists. if it does then you get a message box telling you its in use and to try again later. you can still view the file but you cant edit.

    when editing is done code removes 'file in use', kills the original file and performs 'saveas'.

    don't know if this helps in anyway but it works well for us.

    Doug

    'temporary file' is an incorrect term...it actually copies the data from the original file into my 'application'.


    • Edited by 6da4 Tuesday, February 6, 2018 6:59 PM clarity
    Tuesday, February 6, 2018 6:16 PM
  • Error trapping is NOT the only way... or shouldn't be.I haven't figured this out in detail yet, but it appears that there are a number of file attributes... I would hope that one of them is accessibility. 

    It appears that these are accessible through the GetDetailsOf method for paths through the [shell.application] interface.

    https://docs.microsoft.com/en-us/windows/desktop/shell/folder-getdetailsof

    Monday, January 28, 2019 9:45 PM