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!

If I'm not wrong, when you want to retreive all the files in a directory,
the Dir command should be launched only the first time with the parameters,
after you have to invoke it without parameters.

You should try to place the Dir command before the LOOP line

FM

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