I have a column that contains a file path with the file at the end - all
contained in the cell. There are about 150 rows. I would like to put
something in column be that says - yes the path and file are truly there or
No it is invalid. I have searched multiple places and have not found
anything pointing to how to do this?

I was going to answer as Mike did, but in testing I saw something I'd never
noticed before - if you are looking at a server or shared drive on a
network, referring to one that does not exist with DIR yields an error, not
just an empty string, as Dir usually does. At least I think that's what I'm
seeing. So here's my function:

Function valid_path_and_file(path_and_file As String) As Boolean
Dim tester As String
On Error Resume Next
tester = Dir(path_and_file, vbDirectory)
If Err.Description = "" Then 'If Server5 and SHARE exist
If tester < "" Then
valid_path_and_file = True
End If
End If
On Error GoTo 0
End Function


I called it with this. Again if Server5 or SHARE do not exist, Dir errored
in my test, but the function above did not:

Sub test()
Debug.Print valid_path_and_file("\\Server5\SHARE\GENERAL\test. doc")
End Sub

hth,

Doug

"Rookie_User" wrote in message
...
I have a column that contains a file path with the file at the end - all
contained in the cell. There are about 150 rows. I would like to put
something in column be that says - yes the path and file are truly there
or
No it is invalid. I have searched multiple places and have not found
anything pointing to how to do this?

This seems to work well:

Public Function bFileExists(ByVal sFile As String) As Boolean

Dim lAttr As Long

On Error Resume Next
lAttr = GetAttr(sFile)
bFileExists = (Err.Number = 0) And ((lAttr And vbDirectory) = 0)
On Error GoTo 0

End Function


RBS



"Rookie_User" wrote in message
...
I have a column that contains a file path with the file at the end - all
contained in the cell. There are about 150 rows. I would like to put
something in column be that says - yes the path and file are truly there
or
No it is invalid. I have searched multiple places and have not found
anything pointing to how to do this?

This worked GREAT - thank you.

"Mike" wrote:

You need a function. Open VB editor (ALT + F11) insert a new module and paste
this code in:

Public Function Filethere(strFullPath As String) As Boolean
If Not Dir(strFullPath, vbDirectory) = vbNullString Then Filethere = True
End Function

in b1 next to your filepath in a1 type =filethere(a1)
drag down as required.
It will return true if the path is valid or false.

Will that do?

Mike

"Rookie_User" wrote:

I have a column that contains a file path with the file at the end - all
contained in the cell. There are about 150 rows. I would like to put
something in column be that says - yes the path and file are truly there or
No it is invalid. I have searched multiple places and have not found
anything pointing to how to do this?

I used the above but appreciate this as it will help me when I move the
information over to the shared network from my laptop.

"Doug Glancy" wrote:

I was going to answer as Mike did, but in testing I saw something I'd never
noticed before - if you are looking at a server or shared drive on a
network, referring to one that does not exist with DIR yields an error, not
just an empty string, as Dir usually does. At least I think that's what I'm
seeing. So here's my function:

Function valid_path_and_file(path_and_file As String) As Boolean
Dim tester As String
On Error Resume Next
tester = Dir(path_and_file, vbDirectory)
If Err.Description = "" Then 'If Server5 and SHARE exist
If tester < "" Then
valid_path_and_file = True
End If
End If
On Error GoTo 0
End Function


I called it with this. Again if Server5 or SHARE do not exist, Dir errored
in my test, but the function above did not:

Sub test()
Debug.Print valid_path_and_file("\\Server5\SHARE\GENERAL\test. doc")
End Sub

hth,

Doug

"Rookie_User" wrote in message
...
I have a column that contains a file path with the file at the end - all
contained in the cell. There are about 150 rows. I would like to put
something in column be that says - yes the path and file are truly there
or
No it is invalid. I have searched multiple places and have not found
anything pointing to how to do this?

Personally, I would go with a non-Dir solution.
Using Dir sets the CurDir to that location. Whilst not a major problem, it
may impact your code in unexpected ways; calling GetOpenFilename will then
point to that last location, which is probably meaningless to the user.

NickHK

"Rookie_User" wrote in message
...
This worked GREAT - thank you.

"Mike" wrote:

You need a function. Open VB editor (ALT + F11) insert a new module and
paste
this code in:

Public Function Filethere(strFullPath As String) As Boolean
If Not Dir(strFullPath, vbDirectory) = vbNullString Then Filethere =
True
End Function

in b1 next to your filepath in a1 type =filethere(a1)
drag down as required.
It will return true if the path is valid or false.

Will that do?

Mike

"Rookie_User" wrote:

I have a column that contains a file path with the file at the end -
all
contained in the cell. There are about 150 rows. I would like to put
something in column be that says - yes the path and file are truly
there or
No it is invalid. I have searched multiple places and have not found
anything pointing to how to do this?

NickHK,

I didn't know that. How would you do it?

Doug

"NickHK" wrote in message
...
Personally, I would go with a non-Dir solution.
Using Dir sets the CurDir to that location. Whilst not a major problem, it
may impact your code in unexpected ways; calling GetOpenFilename will then
point to that last location, which is probably meaningless to the user.

NickHK

"Rookie_User" wrote in message
...
This worked GREAT - thank you.

"Mike" wrote:

You need a function. Open VB editor (ALT + F11) insert a new module and
paste
this code in:

Public Function Filethere(strFullPath As String) As Boolean
If Not Dir(strFullPath, vbDirectory) = vbNullString Then Filethere
=
True
End Function

in b1 next to your filepath in a1 type =filethere(a1)
drag down as required.
It will return true if the path is valid or false.

Will that do?

Mike

"Rookie_User" wrote:

I have a column that contains a file path with the file at the end -
all
contained in the cell. There are about 150 rows. I would like to
put
something in column be that says - yes the path and file are truly
there or
No it is invalid. I have searched multiple places and have not found
anything pointing to how to do this?

Doug,
One way is with GetAttr(), as posted by RBS in this thread.
Dir also places a <lock on that folder, so it cannot be deleted, with e.g.
RmDir.

NickHK

"Doug Glancy" wrote in message
...
NickHK,

I didn't know that. How would you do it?

Doug

"NickHK" wrote in message
...
Personally, I would go with a non-Dir solution.
Using Dir sets the CurDir to that location. Whilst not a major problem,
it
may impact your code in unexpected ways; calling GetOpenFilename will
then
point to that last location, which is probably meaningless to the user.

NickHK

"Rookie_User" wrote in message
...
This worked GREAT - thank you.

"Mike" wrote:

You need a function. Open VB editor (ALT + F11) insert a new module
and
paste
this code in:

Public Function Filethere(strFullPath As String) As Boolean
If Not Dir(strFullPath, vbDirectory) = vbNullString Then
Filethere
=
True
End Function

in b1 next to your filepath in a1 type =filethere(a1)
drag down as required.
It will return true if the path is valid or false.

Will that do?

Mike

"Rookie_User" wrote:

I have a column that contains a file path with the file at the
end -
all
contained in the cell. There are about 150 rows. I would like to
put
something in column be that says - yes the path and file are truly
there or
No it is invalid. I have searched multiple places and have not
found
anything pointing to how to do this?

Thanks to you and Rob for this.

Doug

"NickHK" wrote in message
...
Doug,
One way is with GetAttr(), as posted by RBS in this thread.
Dir also places a <lock on that folder, so it cannot be deleted, with
e.g.
RmDir.

NickHK

"Doug Glancy" wrote in message
...
NickHK,

I didn't know that. How would you do it?

Doug

"NickHK" wrote in message
...
Personally, I would go with a non-Dir solution.
Using Dir sets the CurDir to that location. Whilst not a major problem,
it
may impact your code in unexpected ways; calling GetOpenFilename will
then
point to that last location, which is probably meaningless to the user.

NickHK

"Rookie_User" wrote in message
...
This worked GREAT - thank you.

"Mike" wrote:

You need a function. Open VB editor (ALT + F11) insert a new module
and
paste
this code in:

Public Function Filethere(strFullPath As String) As Boolean
If Not Dir(strFullPath, vbDirectory) = vbNullString Then
Filethere
=
True
End Function

in b1 next to your filepath in a1 type =filethere(a1)
drag down as required.
It will return true if the path is valid or false.

Will that do?

Mike

"Rookie_User" wrote:

I have a column that contains a file path with the file at the
end -
all
contained in the cell. There are about 150 rows. I would like to
put
something in column be that says - yes the path and file are truly
there or
No it is invalid. I have searched multiple places and have not
found
anything pointing to how to do this?