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

 

[Andrew Poulsom]

Welcome to the Board.

On which line do you get the error?

 

[DomV]

Heya,

The debugger flags it as:

Set wb = Workbooks.Open(MyPath & "\" & TheFile)

x

 

[jim may]

What is in Cell E29?

DirLoc = Workbooks("Computrawl.xls").Sheets("Computrawl").Range("E29")

 

[RoryA]

Try changing:

ChDir MyPath

to:

ChDrive "R:"
ChDir MyPath

 

[DomV]

What is in Cell E29?

DirLoc = Workbooks("Computrawl.xls").Sheets("Computrawl").Range("E29")

Accounts\October 2009

 

[Andrew Poulsom]

I expect you need to change the drive as well as the directory:

ChDrive "R"
ChDir MyPath

 

[DomV]

Try changing:

ChDir MyPath

to:

ChDrive "R:"
ChDir MyPath

I daresay that has solved it!

You would appear to be a genius sir.

Cheers!

 

[RoryA]

You would appear to be a genius sir.

It's the hat.

 

[Jonmo1]

I would suggest not changing directory/path at all...
It can just get too confusing.
Just specify the correct drive\path in all commands, so you don't have to worry if you're currently in the correct drive\path.

change

MyPath = "R:\" & DirLoc 'path of folder containing xls files
ChDir MyPath
TheFile = Dir("*.xls") 'for all .xls files

to

MyPath = "R:\" & DirLoc 'path of folder containing xls files
TheFile = Dir(MyPath & "\*.xls") 'for all .xls files