Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exclude one folder in a filesearch
I Have this simple macro that will list all the files in a folder including
all the subfolders Sub file_list() With Application.FileSearch ..NewSearch ..LookIn = "C:\My Documents\" ..SearchSubFolders = True ..Filename = "*.*" ..FileType = msoFileTypeAllFiles If .Execute() 0 Then For I = 1 To .FoundFiles.Count Cells(I, 1) = .FoundFiles(I) Next I Else Cells(I, 1) = "No files Found" End If End With End Sub The folder i am looking in being, in this case being C:\My Documents\ has many sub folders. I want it to list the files in all but two of them, is it possible to modify the code to exlcude two specified subfolders. Something along the lines of If .subfolder ="sub1" then goto next thanks in advance Hervinder |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exclude one folder in a filesearch
You could use something like:
Option Explicit Sub file_list() Dim i As Long Dim sCtr As Long Dim SubFoldersToAvoid As Variant Dim myPath As String Dim myFolderName As String Dim SkipIt As Boolean myPath = "C:\my documents" If Right(myPath, 1) < "\" Then myPath = myPath & "\" End If 'no back slashes at the end! SubFoldersToAvoid = Array("excel", "Word") With Application.FileSearch .NewSearch .LookIn = myPath .SearchSubFolders = True .Filename = "*.*" .FileType = msoFileTypeAllFiles If .Execute() 0 Then For i = 1 To .FoundFiles.Count SkipIt = False For sCtr = LBound(SubFoldersToAvoid) _ To UBound(SubFoldersToAvoid) myFolderName = myPath & SubFoldersToAvoid(sCtr) & "\" If UCase(Left(.FoundFiles(i), Len(myFolderName))) _ = UCase(myFolderName) Then SkipIt = True Exit For End If Next sCtr If SkipIt = True Then 'skip it Else Cells(i, 1) = .FoundFiles(i) End If Next i Else Cells(i, 1) = "No files Found" End If End With End Sub ====== Just a warning... xl2007 doesn't support application.filesearch. hervinder wrote: I Have this simple macro that will list all the files in a folder including all the subfolders Sub file_list() With Application.FileSearch .NewSearch .LookIn = "C:\My Documents\" .SearchSubFolders = True .Filename = "*.*" .FileType = msoFileTypeAllFiles If .Execute() 0 Then For I = 1 To .FoundFiles.Count Cells(I, 1) = .FoundFiles(I) Next I Else Cells(I, 1) = "No files Found" End If End With End Sub The folder i am looking in being, in this case being C:\My Documents\ has many sub folders. I want it to list the files in all but two of them, is it possible to modify the code to exlcude two specified subfolders. Something along the lines of If .subfolder ="sub1" then goto next thanks in advance Hervinder -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to List the names of the subfolders present in the folder (path of folder is given in the textbox by user ) | Excel Programming | |||
Save file in a new folder, but create folder only if folder doesn't already exist? | Excel Programming | |||
how can I specific a folder with wildcard criteria and excel will import all the correct files in that folder? | Excel Discussion (Misc queries) | |||
how can I specific a folder with wildcard criteria and excel will import all the correct files in that folder? | Excel Programming | |||
Exclude directory from .FileSearch | Excel Programming |