Hi,

XL 2003

I found the following code from the newsgroup to check if a file exists in a
particular folder.


Sub MyTestFileExists()
Dim FName As String
FName = ThisWorkbook.Sheets("Sheet1").Range("A1").Text
If Dir(FName) = "" Then
Range("B1").Value = "Does not exist"
Else
Range("B1").Value = "Exists"
End If
End Sub

Code works great for one file.

Is it possible to amend this code to loop through a dynamic range,
(currently A1:A200, but will grow) to identify each filename and place the
results in the adjacent cell in column B (B1:B200)?

Any help appreciated.

Thanks

Bob
East Maitland
Australia

You can give this a subroutine a try...

Sub MyTestFileExists()
Dim C As Range
With ThisWorkbook.Sheets("Sheet3")
For Each C In .Range("A1:A" & .Cells(Rows.Count, 1).End(xlUp).Row)
If Dir(C.Text) = "" Then
C.Offset(0, 1).Value = "Does not exist"
Else
C.Offset(0, 1).Value = "Exists"
End If
Next
End With
End Sub

There is no need to specify the range... it will automatically look from A1
to the last used row in Column A.

Rick


"Bob Maloney" <(E-Mail Removed)> wrote in message
news:470887f8$0$14945$(E-Mail Removed)...
> Hi,
>
> XL 2003
>
> I found the following code from the newsgroup to check if a file exists in
> a particular folder.
>
>
> Sub MyTestFileExists()
> Dim FName As String
> FName = ThisWorkbook.Sheets("Sheet1").Range("A1").Text
> If Dir(FName) = "" Then
> Range("B1").Value = "Does not exist"
> Else
> Range("B1").Value = "Exists"
> End If
> End Sub
>
> Code works great for one file.
>
> Is it possible to amend this code to loop through a dynamic range,
> (currently A1:A200, but will grow) to identify each filename and place
> the results in the adjacent cell in column B (B1:B200)?
>
> Any help appreciated.
>
> Thanks
>
> Bob
> East Maitland
> Australia
>

Sub MyTestFileExists()
Dim FName As String

With ThisWorkbook.Sheets("Sheet1")
LastRow = .Cells(Rows.Count, "A"). _
End(xlUp).Row

For RowCount = 1 To LastRow
FName = .Range("A" & RowCount).Text
If Dir(FName) = "" Then
.Range("B" & RowCount).Value = _
"Does not exist"
Else
.Range("B" & RowCount).Value = _
"Exists"
End If
Next RowCount
End With
End Sub


"Bob Maloney" wrote:

> Hi,
>
> XL 2003
>
> I found the following code from the newsgroup to check if a file exists in a
> particular folder.
>
>
> Sub MyTestFileExists()
> Dim FName As String
> FName = ThisWorkbook.Sheets("Sheet1").Range("A1").Text
> If Dir(FName) = "" Then
> Range("B1").Value = "Does not exist"
> Else
> Range("B1").Value = "Exists"
> End If
> End Sub
>
> Code works great for one file.
>
> Is it possible to amend this code to loop through a dynamic range,
> (currently A1:A200, but will grow) to identify each filename and place the
> results in the adjacent cell in column B (B1:B200)?
>
> Any help appreciated.
>
> Thanks
>
> Bob
> East Maitland
> Australia
>
>
>

Thanks Rick and Joel,

Appreciate the prompt assistance.

Cheers

Bob

"Rick Rothstein (MVP - VB)" <(E-Mail Removed)> wrote in
message news:%23aUr$(E-Mail Removed)...
> You can give this a subroutine a try...
>
> Sub MyTestFileExists()
> Dim C As Range
> With ThisWorkbook.Sheets("Sheet3")
> For Each C In .Range("A1:A" & .Cells(Rows.Count, 1).End(xlUp).Row)
> If Dir(C.Text) = "" Then
> C.Offset(0, 1).Value = "Does not exist"
> Else
> C.Offset(0, 1).Value = "Exists"
> End If
> Next
> End With
> End Sub
>
> There is no need to specify the range... it will automatically look from
> A1 to the last used row in Column A.
>
> Rick
>
>
> "Bob Maloney" <(E-Mail Removed)> wrote in message
> news:470887f8$0$14945$(E-Mail Removed)...
>> Hi,
>>
>> XL 2003
>>
>> I found the following code from the newsgroup to check if a file exists
>> in a particular folder.
>>
>>
>> Sub MyTestFileExists()
>> Dim FName As String
>> FName = ThisWorkbook.Sheets("Sheet1").Range("A1").Text
>> If Dir(FName) = "" Then
>> Range("B1").Value = "Does not exist"
>> Else
>> Range("B1").Value = "Exists"
>> End If
>> End Sub
>>
>> Code works great for one file.
>>
>> Is it possible to amend this code to loop through a dynamic range,
>> (currently A1:A200, but will grow) to identify each filename and place
>> the results in the adjacent cell in column B (B1:B200)?
>>
>> Any help appreciated.
>>
>> Thanks
>>
>> Bob
>> East Maitland
>> Australia
>>
>