Restricted Access to Folders

[Atravis]

Hi,

I have built a spreadsheet that imports some data from other workbooks in various locations. If users of the sheet do not have access to these folders the code breaks. I was wondering if there is anyway to check via vba whether a user has access to a folder (and possibly the type of access ie. Read only, read/write) so i could present more informative message boxes instead of having the code bug. Does anyone know of a way to do this?

[mdmackillop]

Test the returned error and handle it in your code

On Error Resume Next
'Your open folder code

'Determine the error- remove when done
MsgBox Err
If Err = xxx Then
MsgBox "not allowed"
Exit Sub
End If
On Error GoTo 0

[Kenneth Hobs]

After checking that it is writeable, you may want to check to make sure that it exists. Dir() can be used for that.

Sub Test_FolderIsWriteable()
MsgBox FolderIsWriteable(ThisWorkbook.Path) 'Will be true if thisworkbook was saved.
MsgBox FolderIsWriteable("vbaexpress") 'Should be false.
MsgBox FolderIsWriteable("d:\") 'Should be false since a readonly cd was inserted.
End Sub

Function FolderIsWriteable(sFolder As String) As Boolean
On Error Resume Next
FolderIsWriteable = (GetAttr(sFolder) And vbReadOnly) <> 1
Exit Function
End Function