Determining Whether File Exists
 

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

Determining Whether File Exists
 

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



"Magnivy" wrote in message
...
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

Determining Whether File Exists
 

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


"Chip Pearson" wrote in message
...
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



"Magnivy" wrote in message
...
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

Determining Whether File Exists
 

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" wrote:

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


"Chip Pearson" wrote in message
...
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



"Magnivy" wrote in message
...
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

Determining Whether File Exists
 

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

"Magnivy" wrote in message
...
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" wrote:

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


"Chip Pearson" wrote in message
...
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



"Magnivy" wrote in
message
...
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

Determining Whether File Exists
 

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


"Chip Pearson" wrote in message
...
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


"Chip Pearson" wrote in message
...
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



"Magnivy" wrote in message
...
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

Determining Whether File Exists
 

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



Magnivy wrote:

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" wrote:

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


"Chip Pearson" wrote in message
...
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



"Magnivy" wrote in message
...
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






--

Dave Peterson

Determining Whether File Exists
 

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
"Chip Pearson" wrote:

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

"Magnivy" wrote in message
...
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" wrote:

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


"Chip Pearson" wrote in message
...
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



"Magnivy" wrote in
message
...
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

Determining Whether File Exists
 

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
"Dave Peterson" wrote:

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



Magnivy wrote:

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" wrote:

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


"Chip Pearson" wrote in message
...
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



"Magnivy" wrote in message
...
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






--

Dave Peterson

Determining Whether File Exists
 

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

"RB Smissaert" wrote:

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


"Chip Pearson" wrote in message
...
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


"Chip Pearson" wrote in message
...
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



"Magnivy" wrote in message
...
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