Runtime Error 1004 cant find the file, but it knows it's name

Hi,

I get the Runtime Error 1004 cant find "Completed Test Survey a.xls" when running the below VBA

Sub CopyPropertyDetails()
Dim myfile As String
Dim erow
myfile = Dir("C:\Users\Paul\Documents\........\Completed Surveys\")

Do While Len(myfile) > 0
If myfile = "ZCollation Tool v1.xlsm" Then
Exit Sub
End If

Workbooks.Open (myfile)
Range("B4:B9").Copy
ActiveWorkbook.Close

erow = CollectionData.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Transpose Destination:=Worksheets("CollectionData").Range(Cells(erow, 1), Cells(erow, 6))

myfile = Dir

Loop

End Sub

I'm trying to open about 150 Excel files, copy the data from a column and transpose it into a blank row of a new summary document called ZCollation Tool v1.xlsm.

Any suggestions?

Thanks