Expert: Tom Ogilvy - 7/9/2009

Question
I have the following code that I wrote in Excel 2003:


Sub Generate_Drawing()
If Sheet10.Range("AN50") = "Drawing Available" Then _

   If Range("AG57").Value = "Your drawing is ready to be generated" Then
   Sheet10.Unprotect Password:="$$$"
   Dim s As String, i As Integer, d As Object
   s = "C:\Images"
   Sheet10.Range("C4").Select
     For Each d In Sheet10.DrawingObjects
      If d.TopLeftCell.Address = "$C$4" Then
          d.Delete
     End If
      Next
   With Application.FileSearch
   .NewSearch
   .LookIn = s
   .SearchSubFolders = True
   .Filename = "*" & Sheet10.Range("E56") & ".EMF"
   .Execute
   For i = 1 To .FoundFiles.Count
   Sheet10.Pictures.Insert (.FoundFiles(i))
  Exit For
  Next i
  End With
  Else
  End If
Sheet10.Protect Password:="$$$"
Else
If Sheet10.Range("AN50") = "Drawing Not Available" Then _
Ans = MsgBox("Drawing Not Available", vbCritical)
End If
End Sub


This Macro basically looks for a file name in cell E56 and and searches for that file in C:\Images and then pastes that image into cell C4. The Problem is when I run this in excel 2007, the line 'With Application.FileSearch' is highlighted as an error and the code does not work. I've seen that this application has been removed from 2007. How can I modify this code so that it works the same way in 2007 as it does in 2003? Thanks so much if you can help me out.

Answer
Melo,

this article explains what you have said and has a link to Microsoft's recommended solution using the scripting runtime or scripting windows search:

http://support.microsoft.com/kb/920229
Error message when you run a macro to search for a file in an Office 2007 program: "Run-time error 5111"


This article also addresses searching directories and subdirectories.
(just provided as an additional source of information).
http://support.microsoft.com/kb/185601/
HOW TO: Recursively Search Directories by Using FileSystemObject

--
Regards,
Tom Ogilvy