Automate identifying if 200 plus files exist in a folder

[Bob Maloney]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Guest]

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]

Thanks Rick and Joel,

Appreciate the prompt assistance.

Cheers

Bob