asked on

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

aikimark

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.

aikimark

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

Lambel

ASKER

@ailimark: Thanks for the hourglass fix.

How about the file search? Any ideas?

aikimark

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

Lambel

ASKER

I am using Excel 2007. Has FileSearch been removed from this version of excel??

ASKER CERTIFIED SOLUTION

aikimark

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Lambel

ASKER

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

Lambel

ASKER

This fixed the hourglass function. I was able to get the folder search working by using the Dir function instead of FileSearch.