EXCEL VBA: Access to a file

What VBA code could I use that  would validate someone has access to a file on a network.

Example:

If Access to N:/FIle1/Files/Statistics/Analysis.xls = True, then
   continue code,
Else
   msgbox "No access to the link."
End If
ouestqueAsked:
Who is Participating?
 
Rgonzo1971Commented:
Hi,

pls try
Function IsFileOpen(filename As String)
    Dim filenum As Integer, errnum As Integer

    On Error Resume Next   ' Turn error checking off.
    filenum = FreeFile()   ' Get a free file number.
    ' Attempt to open the file and lock it.
    Open filename For Input Lock Read As #filenum
    Close filenum          ' Close the file.
    errnum = Err           ' Save the error number that occurred.
    On Error GoTo 0        ' Turn error checking back on.

    ' Check to see which error occurred.
    Select Case errnum

        ' No error occurred.
        ' File is NOT already open by another user.
        Case 0
         IsFileOpen = False

        ' Error number for "Permission Denied."
        ' File is already opened by another user.
        Case 70
            IsFileOpen = True

        ' Another error occurred.
        Case Else
            Error errnum
    End Select

End Function

Open in new window

Refer to https://support.microsoft.com/en-us/help/291295/macro-code-to-check-whether-a-file-is-already-open

Regards
0
 
ouestqueAuthor Commented:
Thanks Rgonzo!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.