I am hoping that someome here can help me out with a problem I am
having.

We distribute our Excel sheets across the nation via a Lotus Notes
database document. In the Excel sheets, when launched, I have some code
to see if a directory exists on the Workbook_Open and the
Workbook_BeforeClose events:

If Len(Dir("MyPath\MyDB.mdb")) = 0 Then Exit Sub

On my server it works as intended.

When the user at another plant opens the document on a different
server, they get a Path Not Found error. The plants other than out
Corporate facility shouldn't have access to this. Why is this? Is there
a solution?

I like to check this way:

dim testStr as string

teststr = ""
on error resume next
teststr=dir("MyPath\MyDB.mdb")
on error goto 0

if teststr = "" then
'not found
else
'found
end if



EAB1977 wrote:

I am hoping that someome here can help me out with a problem I am
having.

We distribute our Excel sheets across the nation via a Lotus Notes
database document. In the Excel sheets, when launched, I have some code
to see if a directory exists on the Workbook_Open and the
Workbook_BeforeClose events:

If Len(Dir("MyPath\MyDB.mdb")) = 0 Then Exit Sub

On my server it works as intended.

When the user at another plant opens the document on a different
server, they get a Path Not Found error. The plants other than out
Corporate facility shouldn't have access to this. Why is this? Is there
a solution?

--

Dave Peterson

Thanks for the reply, Dave

Could I use this instead?

If Dir(MyPath,vbDirectory) = 0 then Exit Sub

Yours is doing the same thing, correct?

Dir is gonna return a string.

Did you mean:
If Dir(MyPath,vbDirectory) = "" then Exit Sub
or
If len(Dir(MyPath,vbDirectory)) = 0 then Exit Sub

And the only difference I've seen is when the MyPath is completely bad--maybe a
badly formed UNC path--or point to a drive that doesn't exist.

Dim myPath As String

myPath = "x:\"
If Len(Dir(myPath & "MyDB.mdb")) = 0 Then
MsgBox "doesn't exist"
End If

myPath = "\\qwerqwer\qwer\qwer\"
If Len(Dir(myPath & "MyDB.mdb")) = 0 Then
MsgBox "doesn't exist"
End If

myPath = "xss:\"
If Len(Dir(myPath & "MyDB.mdb")) = 0 Then
MsgBox "doesn't exist"
End If

The second and third part blew up with:
Run-time error '52':
Bad file name or number

=======
In your original post, I couldn't tell what MyPath was.


EAB1977 wrote:

Thanks for the reply, Dave

Could I use this instead?

If Dir(MyPath,vbDirectory) = 0 then Exit Sub

Yours is doing the same thing, correct?

--

Dave Peterson

I think I got it, Dave.

Since the code was generating a error at the plants, I used this code:

If IsError(Dir("\\files\MyFiles\...")) Then Exit Sub

Dave,

I tried the above code and it still does not work. Maybe I left out a
bit of info.

When the user at another plant runs the code, they get a Path Not Found
error, 76. The path is actually there, just that their security
permissions limit them to what directoreis they can access.

I thought that this would be simple, but the task has been more or a
headache than anything.

I'll try your code to see if it works (kinda bull-headed), and I'll
report back.

If the user is getting the error when they try to save (not open), then they
could have read access, but not write access.

I would think you could modify your code to just check after the attempted save:

on error resume next
'your code to save
if err.number < 0 then
msgbox something went wrong--file not saved
err.clear
end if
on error goto 0



EAB1977 wrote:

Dave,

I tried the above code and it still does not work. Maybe I left out a
bit of info.

When the user at another plant runs the code, they get a Path Not Found
error, 76. The path is actually there, just that their security
permissions limit them to what directoreis they can access.

I thought that this would be simple, but the task has been more or a
headache than anything.

I'll try your code to see if it works (kinda bull-headed), and I'll
report back.

--

Dave Peterson