[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 671
  • Last Modified:

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

0
Lambel
Asked:
Lambel
  • 4
  • 4
1 Solution
 
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
 
LambelAuthor Commented:
@ailimark: Thanks for the hourglass fix.

How about the file search?  Any ideas?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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
 
aikimarkCommented:
I don't know.  I'm using 2003.  The code you posted was my starting point.
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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now