VBA File Search load filenames into an array

I need to build a routine to search a folder and find all the files that meet the criteria, then load them into an array.

I've created a new excel file, and next I want to generate a worksheet for each file in the array.  

1)  I'm having trouble getting the search to find anything.

2)  I can't find a way to count up the files, and use that total to spec out the capacity of the array.

3)  I can't seem to get the array initialized correctly so I can loop through it, and build the worksheets for each filename.

4)  I can't get the DoCmd.Hourglass to compile.  Do I need to declare a library??
Dim NumFound As Long
    Dim i As Integer
	' Find all the files in the folder

       With Application.FileSearch
         .NewSearch
       ' .LookIn = strFolder
         .LookIn = "D:"
         .FileType = msoFileTypeExcelWorkbooks
       ' .TextOrProperty = strFileSpec
         .TextOrProperty = "*.xls"
          If Application.FileSearch.FoundFiles.Count() <= 0 Then    ' is the folder empty?
              MsgBox ("No files found in the directory")
          End If
        End With

        With Application.FileSearch
          .NewSearch
          .LookIn = strFolder
          .FileType = msoFileTypeExcelWorkbooks
          .TextOrProperty = strFileSpec
          .MatchTextExactly = True
        
            ReDim DynFilesFound(1 To 100) As String
            For i = 1 To 100
                DynFilesFound(i) = .FoundFiles(i)
            Next i
        End With

    	    
    'Save the Report WorkBook
    ActiveWorkbook.SaveAs Filename:=strCompleteFileName, FileFormat:=xlNormal, _
        Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
        CreateBackup:=False

    'Modify this into a loop and load worksheets for each filename in the array   	
    'Copy Worksheets into a new workbook
    Sheets(Array("OH COUNT", "number o active RL by customer")).Select
    Sheets("OH COUNT").Activate
    Sheets(Array("OH COUNT", "number o active RL by customer")).Copy


End Sub

Sub CheckPath(strPath As String)
    Select Case blnFileOrDirExists(strPath)
        Case True
            'Do Nothing
        Case Else
            MsgBox "Please enter a valid path", vbCritical, "Generate New RC-T Report File"
            End
        End
    End Select

End Sub

'

Open in new window

LambelAsked:
Who is Participating?
 
aikimarkCommented:
I don't know.  I'm using 2003.  The code you posted was my starting point.
0
 
aikimarkCommented:
DoCmd is an Access VBA environment statement.  You can't use that in your Excel environment.  Comment the statement and see if it compiles cleanly.
0
 
aikimarkCommented:
If you need to change the mouse pointer in Excel, set the application.cursor property.

Hourglass pointer:
Application.Cursor = xlWait

Open in new window



Default pointer:
Application.Cursor = xlDefault

Open in new window

0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LambelAuthor Commented:
@ailimark: Thanks for the hourglass fix.

How about the file search?  Any ideas?
0
 
aikimarkCommented:
What is the blnFileOrDirExists() function?

In order to get a clean compile, I had to define:
   strFolder , strFileSpec , strCompleteFileName
If these are defined elsewhere, comment their local definition.

You were missing the invocation of the .Execute method

Unless you are looking for strings in the contents of the workbooks, you do not need TextOrProperty

The second With...End With block seemed superfluous.

Your If statement used the "Application.FileSearch" object.  Within the With...End With block, you weren't referencing the search you'd just performed.
Dim NumFound As Long
    Dim i As Integer
    Dim strFolder As String
    Dim strFileSpec As String
    Dim strCompleteFileName As String
    
  ' Find all the files in the folder

       With Application.FileSearch
         .NewSearch
         '.LookIn = Environ("homedrive") & Environ("homepath") & "\documents" '"D:"
         .LookIn = "D:"
         
         .FileType = msoFileTypeExcelWorkbooks
         .Execute
          If .FoundFiles.Count <= 0 Then   ' is the folder empty?
            MsgBox ("No files found in the directory")
            Exit Sub
          Else
            ReDim DynFilesFound(1 To .FoundFiles.Count) As String
            For i = 1 To .FoundFiles.Count
                DynFilesFound(i) = .FoundFiles(i)
            Next i
          End If
        End With

'        With Application.FileSearch
'          .NewSearch
'          .LookIn = strFolder
'          .FileType = msoFileTypeExcelWorkbooks
'          .TextOrProperty = strFileSpec
'          .MatchTextExactly = True
'
'            ReDim DynFilesFound(1 To 100) As String
'            For i = 1 To 100
'                DynFilesFound(i) = .FoundFiles(i)
'            Next i
'        End With

          
    'Save the Report WorkBook
    ActiveWorkbook.SaveAs Filename:=strCompleteFileName, FileFormat:=xlNormal, _
        Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
        CreateBackup:=False

    'Modify this into a loop and load worksheets for each filename in the array
    'Copy Worksheets into a new workbook
    Sheets(Array("OH COUNT", "number o active RL by customer")).Select
    Sheets("OH COUNT").Activate
    Sheets(Array("OH COUNT", "number o active RL by customer")).Copy

Open in new window

0
 
LambelAuthor Commented:
I am using Excel 2007.  Has FileSearch been removed from this version of excel??
0
 
LambelAuthor Commented:
aikimark: Thanks for the hourglass fix.  I read that the FileSearch function was removed from Excel 2007 - that might have been my problem.

Either way, I got the code working by using the older Dir function instead.

Thank you for you help,
Lynn
0
 
LambelAuthor Commented:
This fixed the hourglass function.  I was able to get the folder search working by using the Dir function instead of FileSearch.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.