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.

 

[tusharm]

The variable used in the loop is 'file' but the statusbar message and the next line refer to activeworkbook.

 

[seagreen]

Thanks tosharm! How do I write the Debug.Print statement for file? I tried
Debug.Print file
and Debug.Print file.Name

I'm setting "file" to Nothing after I make the "DoSomething" call, right?

Thanks again.

 

[Kenneth Hobson]

You might like one of the routines in this thread.

 

[seagreen]

Thanks for the great resource Kenneth! I see you've been working with this a lot. I will use the code you and others have written to replace the lost FileSearch capability after I get this "quick and dirty" task done. I know I am asking an extremely basic question (I'm just starting in VBA and am not a "real" programmer). So can you or someone tell me how I might get the name of the file in the code I've attempted? I want to use a select case...end case statement to compare the filename and run different code depending on what the filename is (within the "do something subroutine)

Thanks!

 

[seagreen]

Hi,
I tried the following code to try to get the filename:

Sub FindExcelFiles()
Dim foldername As String
Dim FSO As Object
Dim fldr As Object
Dim file As Object
Dim cnt As Long
Dim filename As String
    foldername = "c:\users\bseagreen\desktop\TuesdayFeb102009\week ending feb 7 2009\testing2\"
    Set FSO = CreateObject("Scripting.FilesystemObject")
    Set fldr = FSO.GetFolder(foldername)
    Dim strname As String
    Let strname = Dir$(foldername)

I'm confused about why foldername gives me the filename. Foldername and stringname both give me the same thing, the filename with extension. I looked in the book "VBA and Macros for MS Office Excel 2007", the Case Study on page 113 and 114 about the fact that Excel 2007 doesn't have the application.filesearch method anymore. But I'm confused about getting the filename. I'm a beginner so it's a tough slog for me to figure out. I have been working on this project for about a week now, trying to teach myself some VBA. If it weren't for the help of this board it would probably be 6 months before I could ever hope to get it all myself.
I'm going to keep studying for as l;ong as it takes but can anyone tell me how to get the filename properly? I feel like I don't know what I'm doing when it comes back under "foldername".

Thanks in advance for looking at my "kludge".

 

[seagreen]

Re: Using Filename / Excel 2007 code for file directory loop - can't use Application.Filesearch

Hi,

here's the code I have so far:

Sub FindExcelFiles()
Dim foldername As String
Dim FSO As Object
Dim fldr As Object
Dim file As Object
Dim cnt As Long
Dim filename As String
    foldername = "c:\users\bseagreen\desktop\TuesdayFeb102009\week ending feb 7 2009\testing2\"
    Set FSO = CreateObject("Scripting.FilesystemObject")
    Set fldr = FSO.GetFolder(foldername)
    Dim strname As String
    Let strname = Dir$(foldername)
    Debug.Print strname
    Debug.Print foldername
 
    For Each file In fldr.Files
filename = file
        If file.Type Like "*Microsoft Office Excel*" Then
            cnt = cnt + 1
        End If
        Application.StatusBar = "Now working on " & ActiveWorkbook.FullName
 
        DoSomething ActiveWorkbook, strname
    Next file
 
    'Set file = Nothing
    Set fldr = Nothing
    Set FSO = Nothing
    Range("A1").Value = cnt
 
End Sub
 
Sub DoSomething(inBook As Workbook, strname As String) 'Do whatever you want to each workbook
Debug.Print "Hello"
Debug.Print ActiveWorkbook.FullName
Debug.Print filename
Select Case foldername
Case "microscope1.xls"
Workbooks.Open "microscope1.xls"
c:\users\bseagreen\desktop\TuesdayFeb102009\week ending feb 7 2009\testing2\
Hello
C:\Users\bseagreen\Desktop\TuesdayFeb102009\week ending Feb 7 2009\ALLCMsMergeJan312009.xls
Heidi.xls
Heidi.xls
Hello
C:\Users\bseagreen\Desktop\TuesdayFeb102009\week ending Feb 7 2009\ALLCMsMergeJan312009.xls"
Set wb1 = Workbooks("microscope1.xls")
Set wb2 = ThisWorkbook
For Each wsInBook2 In wb2.Sheets
    For Each wsInBook1 In wb1.Sheets
        If wsInBook1.Name = wsInBook2.Name Then
            wsInBook2.Range("A3:G84").Copy wsInBook1.Range("a3")
            Exit For
        End If
    Next
Next
wb1.Close False
Case Else
Debug.Print strname
End Select
 
End Sub

And here is what's printed in the immediate window:

microscope1.xls
c:\users\bseagreen\desktop\TuesdayFeb102009\week ending feb 7 2009\testing2\
Hello
C:\Users\bseagreen\Desktop\TuesdayFeb102009\week ending Feb 7 2009\ALLCMsMergeJan312009.xls
microscope1.xls
microscope1.xls
Hello

When I F8 through the steps it bypasses case "microscope1.xls". So when it's comparing the string to that filename it doesn't match. What am I doing wrong please? Right now I have Case Else almost empty as I want to debug before I add anything to other cases.

Thanks !!

 

[Kenneth Hobson]

The link and referenced links show how to get the name. If file is an object then fso would get the name I as file.name.

Have you reviewed the fso help file? script56.chm

Other fso help is at Microsoft.

 

[seagreen]

Thanks Kenneth! I tried to use file.name earlier but I guess I need to figure out how to properly qualify it. I looked at this link http://support.microsoft.com/default.aspx?scid=kb;en-us;186118 "How to Use FileSystemObject with Visual Basic" and it shows clearly how to get the filesize (f.size). How to get the name is more subtle. I think I'm in way over my head. I tried a With...End With but the trouble is, I don't know what should go after With.

Also, why do I get the filename when I say folder? I thought folder was Windows speak for directory. I'm still confused. The example shows
Dim f As File but the debugger tells me that file is a user defined type. It doesn't list it as a type (the autocompletion) when I start typing "As". "File" doesn't appear to be a keyword. Is it because I'm in Excel 2007?

As always, thanks, you're really helping me out here and I'm grateful.

 

[seagreen]

Do I need to add a reference (under tools) for file to be recognized as an attribute? Just a wild guess.