Run-time error '1004':File not found

[DomV]

Heya everyone,

I have been having problems with a script and hoped that someone could help.

The script below scans through a specified folder on a network and scans every xls file inside that folder for a value. When it finds the spreadsheet which contains that value (a policy number) it outputs some of the data from the matching file and also provides a link to the sheet in question.

It worked perfectly until I saved, exited and re-opened the file and now I am getting a 1004 run-time error every time I try and execute the script.

I cant for the life of me work out a cause.

More baffling, the wording of the error says that the file 'R:\Dir\Cancellations.xls' could not be found.

At no point is a file called 'Cancellations.xls' specified. Why has the script decided to search for a file called 'Cancellations'?

Baffling,

Thanks in advance,

DomV

Sub Search()
   
    ActiveSheet.unprotect
     ActiveWorkbook.unprotect
Dim wb As Workbook
Dim TheFile As String
Dim MyPath As String
Dim DirLoc As String
Dim PolNum As String
ActiveSheet.Range("C13:G25").ClearContents
Range("E18").FormulaR1C1 = "      Trawling..."
Range("E18").Font.Bold = True
DirLoc = Workbooks("Computrawl.xls").Sheets("Computrawl").Range("E29")
PolNum = Workbooks("Computrawl.xls").Sheets("Computrawl").Range("E9")
Application.ScreenUpdating = False
If Range("E9") = "" Then
Range("E18").FormulaR1C1 = "Please Insert Policy No."
Range("E18").Font.Bold = True
    ActiveSheet.unprotect
     ActiveWorkbook.unprotect
Exit Sub
End If
MyPath = "R:\" & DirLoc     'path of folder containing xls files
ChDir MyPath
TheFile = Dir("*.xls")          'for all .xls files
Do While TheFile <> ""
Set wb = Workbooks.Open(MyPath & "\" & TheFile)
          Windows(TheFile).Activate
        
        If Range("B3").Value = PolNum Then
            Range("A4:D15").Copy
            Windows("Computrawl.xls").Activate
            Range("C13").PasteSpecial xlPasteValues
            Windows(TheFile).Activate
            Application.CutCopyMode = False
            Workbooks("Computrawl.xls").Sheets("Computrawl").Range("C25").Value = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name
            Workbooks("Computrawl.xls").Activate
            Sheets("Computrawl").Range("C25").Select
            ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=Range("C25").Value, TextToDisplay:=Range("C25").Value
            End If
              
       wb.Close SaveChanges:=False                                  'Closes the open file without saving
  TheFile = Dir
Loop
Windows("Computrawl.xls").Activate
If Range("C13").Value = "" Then
Range("C13:G25").Select
ActiveSheet.Range("C13:G25").ClearContents
Range("E18").FormulaR1C1 = "No Records."
Selection.Font.Bold = True
End If
Range("A1").Activate
Application.ScreenUpdating = True
    
    ActiveSheet.protect
     ActiveWorkbook.protect
     
End Sub

 

[jim may]

Op's original code showed:

MyPath = "R:\" & DirLoc 'path of folder containing xls files
ChDir MyPath

Question:
MyPath which is a String = "R:\Accounts\October 2009" 'although the "/" between
'Accts and Oct looks suspicious...
Wouldn't ChDir MyPath switch focus to:
"R:\Accounts\October 2009"

so that
Set wb = Workbooks.Open(MyPath & "\" & TheFile)
wouldn't read:
Set wb = Workbooks.Open(R:\Accounts\October 2009\TheFile)

Sorry, but this is as far as I've ever got into the maze of syntax,
and as you can see -- I might be getting a bit confused.. Can
someone clarify my thinking or mis-thinking. TIA,
Jim

 

[Andrew Poulsom]

The problem is with this:

TheFile = Dir("*.xls")

That returns the name of the first file found in the current path. That may not be the same as MyPath & "\". From Help:

Remarks

The ChDir statement changes the default directory but not the default drive. For example, if the default drive is C, the following statement changes the default directory on drive D, but C remains the default drive:

<CODE>ChDir "D:\TMP" </CODE></PRE>

 

[Jonmo1]

Also, not related to current problem, but to try to avoid future problems...

I'm guessing R is a Mapped Network Drive, right?

I would highly recommend NOT using R in your code.
Use the FULL network path.
\\Sharename\folder\subfolder\filename.xls

If you share the file with others, they might not have the same mapped drives.
If you use the full network path, then it doesn't matter.

 

[DomV]

thats a very good point- i'l try and find out the full network path and correct it asap.

DomV Axiom: Anyone with Calvin in their avatar is unwaveringly correct. =)

Thanks guys, your help is sincerly appriciated.