pawasthi
06-22-2011, 12:02 PM
Hi Friends,
I am using below code to list down all the .xls files in a folder. Its working fine but now I have new requirement to list down all the files in the subfolders also. So I have done some google and found ".SearchSubFolders = False". But this does not work with Excel 2010. So whats the alternative or how I can modify my code to get the file list from sub folders too.
Dim fldr As FileDialog
Dim sItem As String
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
.Title = "Select the Application Folder"
.AllowMultiSelect = False
.InitialFileName = strPath
If .Show <> -1 Then GoTo NextCode
sItem = .SelectedItems(1)
End With
NextCode:
Set fldr = Nothing
FolderName = sItem
wbCount = 0
wbName = Dir(FolderName & "\" & "*.xls")
While wbName <> ""
wbCount = wbCount + 1
ReDim Preserve wbList(1 To wbCount)
wbList(wbCount) = wbName
wbName = Dir
Wend
If wbCount = 0 Then Exit Sub
Set wbThis = ThisWorkbook
I need to get the above code working with the "Searching of files in subfolders (recursively)"
Thanks in advance
I am using below code to list down all the .xls files in a folder. Its working fine but now I have new requirement to list down all the files in the subfolders also. So I have done some google and found ".SearchSubFolders = False". But this does not work with Excel 2010. So whats the alternative or how I can modify my code to get the file list from sub folders too.
Dim fldr As FileDialog
Dim sItem As String
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
.Title = "Select the Application Folder"
.AllowMultiSelect = False
.InitialFileName = strPath
If .Show <> -1 Then GoTo NextCode
sItem = .SelectedItems(1)
End With
NextCode:
Set fldr = Nothing
FolderName = sItem
wbCount = 0
wbName = Dir(FolderName & "\" & "*.xls")
While wbName <> ""
wbCount = wbCount + 1
ReDim Preserve wbList(1 To wbCount)
wbList(wbCount) = wbName
wbName = Dir
Wend
If wbCount = 0 Then Exit Sub
Set wbThis = ThisWorkbook
I need to get the above code working with the "Searching of files in subfolders (recursively)"
Thanks in advance