Excel 2007 Blues Please help

[crenaud]

I'm sort of new to Excel VBA and need some help finding a work around for Application.FileSearch which is no longer supported. The idea is to fill an array with found jpeg file names for use on a form. I use the code below.

Set fs = Application.FileSearch
With fs
.LookIn = modelfolder
.filename = "*.JPG"
If .Execute(SortBy:=msoSortByFileName, SortOrder:=msoSortOrderAscending) > 0 Then
ReDim pics(.FoundFiles.Count)
NOPIC = .FoundFiles.Count

For i = 1 To .FoundFiles.Count
filename = .FoundFiles(i)
pics(i) = Mid(filename, InStrRev(filename, "\") + 1)
Next i

End If
End With

Could someone help with a solution for this in Excel 2007?

 

[Jon Peltier]

The function uses Dir to return an array of filenames.

Function GetFilesFromDirectory(sDirectory As String, sFilter As String) As String()
  Dim sFileName() As String
  Dim sTemp As String
  Dim i As Long
  i = 0
  
  sTemp = Dir(sDirectory & "\" & sFilter)
  
  Do While Len(sTemp) > 0
    i = i + 1
    ReDim Preserve sFileName(1 To i)
    sFileName(i) = sTemp
    sTemp = Dir()
  Loop
  
  GetFilesFromDirectory = sFileName
End Function

In your example, you call it using this:

Dim sFileNames() as String
sFileNames = GetFilesFromDirectory(modelfolder, "*.jpg")

Use sFileNames in place of .FoundFiles(i)