Excel 2007 code for file directory loop - can't use Application.Filesearch

[seagreen]

Hello,
I have been trying to process Excel files in a directory with the following code:

Sub FindExcelFiles()
Dim foldername As String
Dim FSO As Object
Dim fldr As Object
Dim file As Object
Dim cnt As Long
    foldername = "c:\users\bseagreen\desktop\TuesdayFeb102009\week ending feb 7 2009\testing2\"
    Set FSO = CreateObject("Scripting.FilesystemObject")
    Set fldr = FSO.GetFolder(foldername)
    For Each file In fldr.Files
        If file.Type Like "*Microsoft Office Excel*" Then
            cnt = cnt + 1
        End If
        Application.StatusBar = "Now working on " & ActiveWorkbook.FullName
        DoSomething ActiveWorkbook
    Next file
    Set file = Nothing
    Set fldr = Nothing
    Set FSO = Nothing
    Range("A1").Value = cnt
End Sub

Here's the stub for the subroutine that's being called:

Sub DoSomething(inBook As Workbook) 'Massage each workbook
'Debug.Print "Hello"
Debug.Print ActiveWorkbook.FullName
End Sub

I am using Excel 2007. I found out I cannot use Application.Filesearch as Microsoft has dropped this method for 2007. My problem now is that I just see "Now working on c:\users\bseagreen\desktop\TuesdayFeb102009\week ending feb 7 2009\testing2\file1.xls written six times in the immediate window. Can anyone help me figure out why the code isn't looping through all the files. I'm new at VBA and am probabluy missing something obvious.

Thanks in advance for your help.

 

[Kenneth Hobson]

You are using a late binding method. Use early binding so that intellisense can help show the properties and methods of filescripting objects.

I don't have 2007 so I am not sure what conflict File might cause. You might prefix it with "o" to remind you that it is an Object and not a String.
e.g.

Dim oFile as Object
'replace your line
filename = oFile.Name 'Just filename part
'filename = oFile.FullName 'Includes file path with the filename.

I include this next part to show you how to use early binding for a few basic fso methods.

Sub fsoGetSpecialFolder2()
Rem http://www.ozgrid.com/forum/showthread.php?t=64700
Rem Needs Reference: MicroSoft Script Runtime, scrrun.dll
Rem Instructions: http://support.microsoft.com/default.aspx?scid=kb;en-us;186118
    Dim FSO As FileSystemObject
    Set FSO = New FileSystemObject
    MsgBox FSO.GetSpecialFolder(WindowsFolder)
    Set FSO = Nothing
End Sub
Function MyDocuments() As Variant
Rem Needs Reference: MicroSoft Script Runtime, scrrun.dll
Rem Instructions: http://support.microsoft.com/default.aspx?scid=kb;en-us;186118
    Dim FSO As FileSystemObject
    Set FSO = New FileSystemObject
    MyDocuments = FSO.GetSpecialFolder(4)
    Set FSO = Nothing
End Function

Here is an fso method to put *.txt or *.xls filenames in the main folder and subfolders into column A.

Sub Test()
  ShowFiles DesktopFolder, "*.txt"
End Sub

Sub Test2()
  ShowFiles "x:", "*.xls"
End Sub

Sub Test3()
  ShowFiles "x:", "*.xls", False
End Sub

'Similar to: NateO's code, http://www.mrexcel.com/forum/showpost.php?p=1228168&postcount=2
Sub ShowFiles(strDir As String, searchTerm As String, _
  Optional subFolders As Boolean = True)
  Dim fso As Object
  Dim strName As String
  Dim i As Long
  ReDim strArr(1 To Rows.Count, 1 To 1) As String
  
  'strDir must not have a trailing \ for subFolders=True
  If Right(strDir, 1) <> "\" Then strDir = strDir & "\"
  
  'Exit if strDir does not exist
  If Dir(strDir, vbDirectory) = "" Then Exit Sub
    
  Let strName = Dir$(strDir & searchTerm)
  Do While strName <> vbNullString
      Let i = i + 1
      Let strArr(i, 1) = strDir & strName
      Let strName = Dir$()
  Loop
  Set fso = CreateObject("Scripting.FileSystemObject")
  'Strip trailing \ if subFolders=False
  If subFolders = False Then strDir = Left(strDir, Len(strDir) - 1)
  Call recurseSubFolders(fso.GetFolder(strDir), strArr(), i, searchTerm)
  Set fso = Nothing
  If i > 0 Then
      Range("A1").Resize(i).Value = strArr
  End If
End Sub

'script56.chm, http://tinyurl.com/5ts6r8
'Instructions: http://support.microsoft.com/default.aspx?scid=kb;en-us;186118
Rem Needs Reference: MicroSoft Script Runtime, scrrun.dll
Private Sub recurseSubFolders(ByRef Folder As Object, _
    ByRef strArr() As String, _
    ByRef i As Long, _
    ByRef searchTerm As String)
Dim SubFolder As Object
Dim strName As String
For Each SubFolder In Folder.subFolders
    Let strName = Dir$(SubFolder.Path & "\" & searchTerm)
    Do While strName <> vbNullString
        Let i = i + 1
        Let strArr(i, 1) = SubFolder.Path & "\" & strName
        Let strName = Dir$()
    Loop
    Call recurseSubFolders(SubFolder, strArr(), i, searchTerm)
Next
End Sub

Function DesktopFolder()
  Dim wshShell As Object
  Set wshShell = CreateObject("WScript.Shell")
  DesktopFolder = wshShell.specialfolders("Desktop")
End Function

Sometime, I may work up an example xls that includes all the various method to fill an array with filenames from the subfolders. I will wait a bit to see if the class method in that other thread is modified.

 

[seagreen]

Thanks. I'm just a beginner so I don't know what late and early binding are yet. I haven't had any computer sci classes - just biology, chem, math, etc. I did need to select (check the box) for the reference "Windows scripting runtime" and then it recognized "file" as a key word.

Tomorrow I will regroup and see if I can post a simpler example so it's more clear what I'm asking. In a nutshell, I am wondering if there is a straightforward attribute (maybe I'm using the work incorrectly here), that will contain the filename. Analogous to f.size where f is your file object.

Thanks so much Kenneth for all your brilliant help; someday I aspire to understanding it

 

[seagreen]

I will look at the latest examples you have provided Kenneth, to see if I can learn enough about fso to get me going. You surely have provided a lot of good information. I cannot wait until I can see what attribute returns the name for a file.
Thanks again.

 

[Kenneth Hobson]

Here is an article on early vs. late binding. http://support.microsoft.com/kb/245115

Once you set the reference in Tools > References... > Microsoft Scripting Runtime:

Sub FSOWorkbookInfo()
Rem Needs Reference: MicroSoft Script Runtime, scrrun.dll
    Dim FSO As FileSystemObject
    Dim f As Scripting.File
    Dim fldr As Scripting.Folder

    Set FSO = New FileSystemObject
    Set fldr = FSO.GetFolder(ThisWorkbook.Path)
    Set f = FSO.GetFile(ThisWorkbook.FullName)

    MsgBox "ThisWorkbook.Path: " & fldr.Path
    MsgBox "Files in " & fldr.Name & ": " & fldr.Files.Count
    MsgBox "ThisWorkbook.Name: " & f.Name
    MsgBox "ThisWorkbook.Fullname: " & f.Path

    Set FSO = Nothing
    Set f = Nothing
    Set fldr = Nothing
End Sub

 

[seagreen]

Thanks Kenneth!!

 

[Starbucks_33]

I was working on the same problem --> listing filenames in a directory.

Just wondering, cant you just use the "Dir" function?

Or does that have limitations?

Sub Main()
Dim array_of_filenames(1 To 1000, 1 To 5) As String
Dim i As Integer
Dim xx As String

xx = "C:\"
Call CreateArrayOfFileNames(xx, array_of_filenames)
For i = 1 To 100
Cells(i, 1) = array_of_filenames(i, 1)
Next i
End Sub


Sub CreateArrayOfFileNames(in_directory_of_files, array_of_filenames)
Dim ff As String
Dim rr As Long
ff = Dir(in_directory_of_files, 7)

Do While ff <> ""
rr = rr + 1
array_of_filenames(rr, 1) = ff
array_of_filenames(rr, 2) = FileLen(in_directory_of_files & ff)
array_of_filenames(rr, 3) = FileDateTime(in_directory_of_files & ff)
' Get next file
ff = Dir
Loop

End Sub

 

[Starbucks_33]

2

 

[Kenneth Hobson]

No. Dir only searches that one folder. Notice how I did use Dir but use fso methods to get the subfolders. Of course one can use all fso since we are going to the trouble of using it to get subfolders.

If you have Excel2003, FileSearch is the best route. This thread dealt with alternatives. I like the class method in the thread that I referenced. As most would use Application.FileSearch, you can use that method.

 

[tusharm]

For two modularized approaches that use Dir, see my
Process all files in a folder and, optionally, in sub-folders
http://www.tushar-mehta.com/publish_train/xl_vba_cases/process_all_files_in_folder.htm
and Karl Peterson's http://vb.mvps.org/samples/project.asp?id=DirDrill

 

[seagreen]

Thanks Starbucks 33! I have been in classes all day but will try your ideas this evening hopefully. I will post again when I have an update. I am using Excel 2007 so cannot use FileSearch unfortunately as someone else mentioned this. Having the ability to traverse subdirectories would be very useful but I can work around if necessary.