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
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
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]
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber