Determining Whether File Exists

[Guest]

Greetings!

I have a macro that opens files for which the path is indicated in a range,
say A1:A50. Before I run the macro, I would like to test whether the file
paths are entered correctly. I'm trying to create a macro that would test
whether the files contained in cells A1:A10 exist, but cant come up with
anything.

Any insight you provide would be greatly appreciated.

Sincerely,

Magnivy

 

[Chip Pearson]

Try something like


Dim Rng As Range
For Each Rng In Range("A1:A10")
If Dir(Rng.Text) <> "" Then
' file exists
Else
' file doesn't exist
End If
Next Rng


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Chip Pearson]

Better code:

Dim Rng As Range
For Each Rng In Range("A1:A10")
If Rng.Text <> "" Then
If Dir(Rng.Text) <> "" Then
' file exists
Else
' file doesn't exist
End If
End If
Next Rng




--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Guest]

Chip, thanks a lot for your help. I've been trying to run your macro, but an
error messege pops up. Its says "Run-time error 52: bad file name or number."

Would you know whats causing it?

 

[Chip Pearson]

I can't reproduce the problem you describe. Usually, you use that
error when performing file IO operations. Are you getting the
error with the EXACT code I posted, or have you modified the
code? Post your code. What is in the cell referenced by Rng when
the error occurs?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[RB Smissaert]

Even better:

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

 

[Dave Peterson]

Sometimes the contents of the cell are enough to break the dir() command.

I like this modified version of Chip's routine:

Dim Rng As Range
dim TestStr as string
For Each Rng In Range("A1:A10").cells
If Rng.Text <> "" Then

teststr = ""
on error resume next
teststr = dir(rng.text)
on error goto 0

If teststr <> "" Then
' file exists
Else
' file doesn't exist
End If
End If
Next Rng

 

[Guest]

Chip,

I see why it wasnt working out. The syntax of the file path was incorrect.
It now works great. Thank you very much for your help!

Magnivy

 

[Guest]

Dave,

I see why Chip's wasnt working out. The syntax of the file path was
incorrect. It now works great. Thank you very much for your help!

Magnivy

 

[Guest]

RB,

Thank you for your help! For function works great ! Would you mind to
briefly explain how does the fifth line work ["bFileExists = (Err.Number = 0)
And ((lAttr And vbDirectory) = 0)"]. I'm new to creating custom functions and
am trying to learn as much as possible.

Thanks a lot!

Magnivy