Application.FileSearch runtime error '455' in Excel 2007

I have seen some of the other solution here on EE but I don't how to implement them to fit my situation. Here is my code that attempts to look for a file within a specified folder based on county, year, and status. If not found, a message box pops saying "File not found.  Not Created Yet".

I just need to know how to re-arrange the variables I have already set to work with the workaround method for Excel 2007. This file was initially created in Excel 2003.
Sub CheckTargetFile()
   Targetfile1 = "SR_" & cnty & ".txt"
   Dirname1 = "H:\srprod\Data Requests\" & typestudy & "Files\" & year
 
 
   'To make sure that file exists
   With Application.FileSearch
        .NewSearch
        .LookIn = Dirname1
        .Filename = Targetfile1
        If .Execute() = 0 Then
            MsgBox "File not found.  Not Created Yet", vbOKOnly
        Else
            Call Import
        End If
    End With

Open in new window

angelmtaylorAsked:
Who is Participating?
 
Patrick MatthewsCommented:
Hello angelmtaylor,

FileSearch was dropped from Office 2007.  Instead, try:

If Dir(Dirname1 & "\" & Targetfile1) <> "" Then
    'code for file exists
Else
   'does not exist
End If

Regards,

Patrick
0
 
dlc110161Commented:
Where do you define what typestudy and year contain? They both look like variables, but I don't see where you have values being passed into them.

Dawn Crosier-Bleuel
Word MVP
0
 
angelmtaylorAuthor Commented:
I have attached the code and form that sends the variable values.
Sub Button10_Click()
   If Range("P1").Value = 1 Then
     year = "2000"
   ElseIf Range("P1").Value = 2 Then
     year = "2001"
   ElseIf Range("P1").Value = 3 Then
     year = "2002"
   ElseIf Range("P1").Value = 4 Then
     year = "2003"
   ElseIf Range("P1").Value = 5 Then
     year = "2004"
   ElseIf Range("P1").Value = 6 Then
     year = "2005"
   ElseIf Range("P1").Value = 7 Then
     year = "2006"
   ElseIf Range("P1").Value = 8 Then
     year = "2007"
   End If
 
   If Range("r1").Value < 10 Then
      cnty = "00" & CStr(Range("r1").Value)
   ElseIf Range("r1").Value < 100 Then
      cnty = "0" & CStr(Range("r1").Value)
   Else
      cnty = CStr(Range("r1").Value)
   End If
   
   If Range("T1").Value = 1 Then
     typestudy = "Prelm"
   ElseIf Range("T1").Value = 2 Then
     typestudy = "Study"
   End If
 
   
   Call CheckTargetFile
 
End Sub

Open in new window

form.JPG
0
 
angelmtaylorAuthor Commented:
Thank you so much!! It was plug and go with your solution. I don't know why I didn't think if it. You truly are an EXPERT!!  Thanks for your help!!
0
 
Patrick MatthewsCommented:
angelmtaylor,

You are most welcome :)

Regards,

Patrick
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.