does file exist
 

Hi

I have a list of files, with the directory path from cells a1 to a10
and want to check to see if any do not exist. I would like the file
names assigned to an array in vba, check them off and if one does not
exist stop the code running with an error message.

Here is what I have works well for one file.


Sub test2()

Dim sName As String

sName = Range("A2").Value
If Dir(sName) < "" Then
'do nothing in this case
Else
MsgBox "file Not found, stop the code at this point"
'file was found
End If

End Sub

Thanks

Chad

does file exist
 

I'm not sure I'd use an array but to each his own
Dim sNames() as String
Dim cnt as Long
Dim lRow as Long
Set lRow = Range("A" & Rows.Count).End(xlUp).Row
For cnt = 2 to lRow
ReDim Preserve sNames(cnt - 2)
sNames(cnt - 2) = Range("A" & cnt)
If Dir(sName(cnt - 2)) < "" Then
MsgBox "Error File Not Found"
Exit Sub
End If
Next

HTH

Charles Chickering

Chad wrote:
Hi

I have a list of files, with the directory path from cells a1 to a10
and want to check to see if any do not exist. I would like the file
names assigned to an array in vba, check them off and if one does not
exist stop the code running with an error message.

Here is what I have works well for one file.


Sub test2()

Dim sName As String

sName = Range("A2").Value
If Dir(sName) < "" Then
'do nothing in this case
Else
MsgBox "file Not found, stop the code at this point"
'file was found
End If

End Sub

Thanks

Chad

does file exist
 

Sub test2()
Dim cell as Range
Dim sName As String
for each cell in Range("A1:A10")
sName = cell.Value
If Dir(sName) = "" Then
MsgBox "file Not found, stop the code at this point"
Exit for
End If
Next cell
End Sub


--
Regards,
Tom Ogilvy


"Chad" wrote in message
ups.com...
Hi

I have a list of files, with the directory path from cells a1 to a10
and want to check to see if any do not exist. I would like the file
names assigned to an array in vba, check them off and if one does not
exist stop the code running with an error message.

Here is what I have works well for one file.


Sub test2()

Dim sName As String

sName = Range("A2").Value
If Dir(sName) < "" Then
'do nothing in this case
Else
MsgBox "file Not found, stop the code at this point"
'file was found
End If

End Sub

Thanks

Chad

does file exist
 

Yep

Exactly what I was looking for. Thank you both very much for your
help.

Take care

Chad