Update macro application search to V2007

[naris]

Dear Forum members,

Could you help me, how to update my excel macro for application.file search from excel 2003 to excel 2007 or higher.
Because I got error when run my macro.
Please see my macro for the detail.

Thanks in advance
regards,
naris

[Bob Phillips]

You need to use FSO.

Here is an example of iterating sub-folders that you can incorporate into your code

[vba]Dim FSO As Object
Dim cnt As Long
Dim arfiles
Dim level As Long

Sub Folders()
Dim i As Long
Dim sFolder As String

Set FSO = CreateObject("Scripting.FileSystemObject")

arfiles = Array()
cnt = -1
level = 1

sFolder = "C:\myTest"
ReDim arfiles(1, 0)
If sFolder <> "" Then
SelectFiles sFolder
Worksheets.Add.Name = "Files"
With ActiveSheet
For i = LBound(arfiles, 2) To UBound(arfiles, 2)
.Hyperlinks.Add Anchor:=.Cells(i + 1, arfiles(1, i)), _
Address:=arfiles(0, i), _
TextToDisplay:=arfiles(0, i)
Next
.Columns("A:Z").EntireColumn.AutoFit
End With
End If

End Sub

'-----------------------------------------------------------------------
Sub SelectFiles(Optional sPath As String)
'-----------------------------------------------------------------------
Dim fldr As Object
Dim Folder As Object
Dim file As Object
Dim Files As Object

If sPath = "" Then
Set FSO = CreateObject("SCripting.FileSystemObject")
sPath = "c:\myTest"
End If

Set Folder = FSO.GetFolder(sPath)

Set Files = Folder.Files
For Each file In Files
cnt = cnt + 1
ReDim Preserve arfiles(1, cnt)
arfiles(0, cnt) = Folder.path & "\" & file.Name
arfiles(1, cnt) = level
Next file

level = level + 1
For Each fldr In Folder.Subfolders
SelectFiles fldr.path
Next
End Sub
[/vba]