dir(...) will return the name of the file--not including the path.

Try changing:
Set mybook = Workbooks.Open(filnames, , True)
to
Set mybook = Workbooks.Open(mypath & filnames, , True)

Depending on what myPath is, it may work the way you want.

keri wrote:

Hi,

I have the code below that opens each file in a folder, finds a value
and returns it to another sheet. However I am having a problem with
the code.

rnum = 1
Dim wkb As Workbooks
Dim filnames
filnames = Dir(mypath & "*" & ".xls")
Do While filnames < ""
Set mybook = Workbooks.Open(filnames, , True)
For Each sh In mybook.Worksheets
Set sourcerange = sh.Range("a100:p1000")
With sourcerange
Set rng = .Find(WHAT:="TOTAL")
If Not rng Is Nothing Then
basebook.Sheets("sheet1").cells(rnum, "B").Value = mybook.Name
basebook.Worksheets("SHEET1").cells(rnum, "C").Value =
rng.Offset(0, 1).Value
rnum = rnum + 1
Else
'do nothing
End If
End With
Next sh
mybook.Close savechanges:=False
Loop
End If

I get a runtime error '1004'
'ORDER21107.XLS' could not be found.Check the spelling of the file
name, and verify that the file location is correct.

(This debugs on the line
Set mybook = Workbooks.Open(filnames, , True))

I have checked the path is correct (and I know it must be as the error
is returning a filename in the correct folder) so it seems to be
another problem to this.

Any help would be appreciated before I pull all my hair out!

--

Dave Peterson