naris
04-18-2012, 10:08 PM
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
04-19-2012, 12:20 AM
You need to use FSO.
Here is an example of iterating sub-folders that you can incorporate into your code
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.