change code from filesearch to FSO and Dir

**canedje** · 03-18-2010, 08:15 AM

Hello everybody.

i did write some code in VBA reading all files with fitting a wildcard format from a given directory and all subdirectorys and place the found files and path in an excel sheet.

I used herefore the Application.FIleSearch object.
The problem is that this sometimes not working with Windows 7 or is not always recognizing "zip" files.

I Googled on internet and learned that it will be better to use FileSystemObject and Dir to do the job, because FileSearch is not allways supported by the system.

I did try to translate the code, but couldn't work it out.

How to translate the next code in the other FSO and Dir format?

Code:

Set bz = Application.FileSearch
With bz
    .LookIn = FilePad
    .SearchSubFolders = True
    .Filename = wildC
    If .Execute(SortBy:=msoSortByNone, SortOrder:=msoSortOrderAscending) > 0 Then
        Sheets(Sheetnames(1)).Range("p5").Value = .FoundFiles.Count       'Plaats hoeveelheid files in P5
  
        For i = 1 To .FoundFiles.Count
            Sheets(Sheetnames(1)).Select
            Range("A" & i + offset).Value = .FoundFiles(i)         'ZET FILENAME IN KOLOM A
            plek = Testlocatie(i + offset, plek)                   'TEST OF THE FILE NAAM IN BEIDE SHEETS OVEREENKOMEN OP DEZELFDE LOCATIE
            plek = plek + 1
        Next
    End If
    Set bz = Nothing

wereby WildC is the wildcard to search for and Filepad is the starting directory to look from including all subdirectorys?

Any help would be preciated.

**canedje** · 03-18-2010, 08:56 AM

I did found the next code somewhere at this forum. (a littlebit modified by myself)
Doing almost the job.

Only thing missing is the possibility to add an search wildcard for the files to display.
For example "*.xls" as a wildcard is showing only the excel files instead of all files like the code below.

Is this code easy to change to my wishes and add a wildcard option?

Code:

Sub ListFiles(mypath As String, myrow As Integer)

Dim objFSO As FileSystemObject
Dim objFol As Folder
Dim objSubFol As Folder
Dim objFiles As Files
Dim objFile As File


Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFol = objFSO.GetFolder(mypath)
Set objFiles = objFol.Files


For Each objFile In objFiles
    myrow = myrow + 1
    Cells(myrow, 2) = objFile.Name
    Cells(myrow, 1) = mypath & "\" & objFile.Name
Next


'Find subfolders and get the macro to call itself
'using subfolder as the path

For Each objSubFol In objFol.SubFolders
  ListFiles (objSubFol.Path), myrow
Next


Columns("A:C").EntireColumn.AutoFit

Set objFSO = Nothing
Set objFol = Nothing
Set objSubFol = Nothing
Set objFile = Nothing
Set objFiles = Nothing

End Sub




Sub getAllFiles()

Dim mypath As String
Dim myrow As Integer

mypath = "C:\data"
myrow = 1

Call ListFiles(mypath, myrow)

End Sub

**canedje** · 03-18-2010, 01:56 PM

I installed the last code from above in the total program I wrote.
It works fine.

I only do need a function returning false or true if an string is fitting with an wildcard.
for example:

function compare(wildcard as string, filename as string) as boolean

Function compare is true when: wildcard is "*.xls" and filename is "test.xls"
Function compare is false when: wildcard is "*.doc" and filename is "test.xls"
etc.

can anybody help with some code? Or give hints where to find an example?

Thread: change code from filesearch to FSO and Dir

Thread Tools

Display

change code from filesearch to FSO and Dir

Re: change code from filesearch to FSO and Dir

Re: change code from filesearch to FSO and Dir

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions