Check access right to folder

[Charles]

Hi

Actually an easy one but I can't find anything on google. How can I
check the access right to a folder (I just want to know if the user of
the macro has access to a shared folder)? Is there any function that
does that? (that would be on a network drive)

thanks in advance
Charles

 

[JLGWhiz]

The manual check is to open the workbook, then Tools>SharedWorkbook and check
the list of users in Who has it open window.

 

[Charles]

Sorry, I meant a windows shared drive folder. Is there any way to do
something like hasaccesstofolder("C:\Folderx\") which would tell me if
the user running the macro has access to the folder

Charles

 

[JLGWhiz]

I have never used it, but check the UserStatus Property in VBA help. It
looks close to what you are describing.

 

[Bob Bridges]

I've never had occasion to use them, but there are objects designed for
network administrators; seems to me one of those would tell you what you
needed to know.

Of course, this would be a lot more useful if I could actually point to a
URL documenting one of them, and my screen is kinda full up; I'm not sure
where to look. But if you email me later, I'm sure I can find at least the
general area I saw it in before. Something around MS' scripting
documentation.

 

[Tim Williams]

Have you tried

Dir("\\somedrive\somefolder")

?

Tim

 

[urkec]

I've never had occasion to use them, but there are objects designed for
network administrators; seems to me one of those would tell you what you
needed to know.

Of course, this would be a lot more useful if I could actually point to a
URL documenting one of them, and my screen is kinda full up; I'm not sure
where to look. But if you email me later, I'm sure I can find at least the
general area I saw it in before. Something around MS' scripting
documentation.

There is a sample here:


http://www.microsoft.com/technet/scriptcenter/scripts/security/dacls/sedcvb02.mspx

 

[Dave Peterson]

Depending on what you're testing, why not just create a text file and try
saving/deleting/renaming... the file. If it fails on one of them, then they
don't have that kind of access.

 

[Charles]

Hi Dave

That's what I was thinking to initially. But I am a bit concerned
about having a dozen of users doing it all the time and the potential
pollution if the macro fails and files keep accumulating.

Ideally it would be a function that does something like reading the
directory (or anything else) and return an error I can identify like
either "not found" (if the network drive has another letter assigned
for this user) or "access denied" if the user does not have the proper
access rights. I guess I can use the Dir function as Tim suggested and
use "on Error" statement to return if the user has access or not. But
I don't know if there is a better function that would return an error
code corresping to the inability to access the file instead of failing
and creating an error in VBA.

Charles

 

[Dave Peterson]

What rights do you want the user to have?

Being able to open the file doesn't mean that they could update/save the file.

 

[Charles]

I am just interested in the ability to read the file. So I guess
enything that could list the content of the folder would do the trick.
I did this:

Public Function HasAccessToFolder(ByVal FolderPath As String) As
Boolean
Dim St As String
On Error GoTo ErrorHdlr
HasAccessToFolder = True
St = Dir(FolderPath)
Exit Function
ErrorHdlr:
HasAccessToFolder = False
Exit Function
End Function

but it obviously doesn't tell me what is wrong, just that something is
wrong.

Charles

 

[Dave Peterson]

If you're only interested if the user can open an excel file, you could turn off
error checking, try to open it readonly. If it's successful, then they have
access to it.

Dim wkbk as workbook
set wkbk = nothing
on error resume next
set wkbk = workbooks.open(filename:="path.to.excel\something.xls", _
readonly:=true)
on error goto 0

if wkbk is nothing then
'no access!
else
'have access and the file is open
end if

 

[Charles]

Well, in this case I am interested in opening a huge access file. So
what I try to do is to first look at if I can have access to the
folder, and then only I attempt a (time consuming) connection.

 

[Dave Peterson]

If you don't have access to the folder, then I bet that the time consumed will
be small.