[Last Call] Learn how to a build a cloud-first strategyRegister Now

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

excel vba arrays file search

In excel vba, I need to search a folder for files, and load them into a string array.  I'm having trouble working with the array.  Can someone tell me how to initial the array, set the capacity to the value that is the total number of files in the folder, and then loop through the files in the folder and load them into the array?
Thanks,
Lynn
' File Folder Search
 ' Returns a string listing files found in strFolder
  Function FileList(strInputFolder As String, Optional strfilter As String = "*.*") As Variant

    Dim strHolder As String
    Dim i As Integer
    Dim strTemp As String
    Dim strTemp2 As String
    

    If Right$(strInputFolder, 1) <> "\" Then strInputFolder = strInputFolder & "\"
    strTemp = Dir(strInputFolder & strfilter)
    ' make sure there are files in the folder
    If strTemp = "" Then
        MsgBox "The folder is empty.", vbCritical, "File Search"
        Exit Function
    End If

    ' count number of files
    Call FileCount(strInputFolder, strfilter)

   'load filenames into array
      
      Dim arrayFiles(0 To FileCount(strInputFolder, strfilter) + 1) As String    'DOESN'T COMPILE - FileCount - "Constant expression required"
      For i = LBound(arrayFiles) To UBound(arrayFiles)
        arrayFiles(i) = Dir(strInputFolder & strfilter)
      Next i
     FileList = arrayFiles()

End Function

'get file count
Function FileCount(strInputFolder As String, strfilter As String) As Long
    Dim strTemp2 As String
    Dim lngCount As Long
    Dim MyArray As String
    Dim i As Integer
    
    strTemp2 = Dir(strInputFolder & strfilter)
    Do While strTemp2 <> ""
        lngCount = lngCount + 1
        strTemp2 = Dir
        MyArray(i) = strTemp2        ' DOESN'T COMPILE!!!!
        Debug.Print (MyArray(i))
        i = i + 1
    Loop
    
    FileCount = lngCount

End Function

Open in new window

0
Lambel
Asked:
Lambel
  • 3
  • 2
1 Solution
 
sdwalkerCommented:
The easy thing to do is just oversize the array (as in the code below).  There's probably a way to get a count of the files without looping through them first and then you can re-dimension the array using the ReDim statement.

I'll see if I can find a quick filecount without looping and post back here.

sdwalker
' File Folder Search
 ' Returns a string listing files found in strFolder
  Function FileList(strInputFolder As String, Optional strfilter As String = "*.*") As Variant

    Dim strHolder As String
    Dim i As Integer
    Dim strTemp As String
    Dim strTemp2 As String
    

    If Right$(strInputFolder, 1) <> "\" Then strInputFolder = strInputFolder & "\"
    strTemp = Dir(strInputFolder & strfilter)
    ' make sure there are files in the folder
    If strTemp = "" Then
        MsgBox "The folder is empty.", vbCritical, "File Search"
        Exit Function
    End If

    ' count number of files
    Call FileCount(strInputFolder, strfilter)

   'load filenames into array
      
      Dim arrayFiles(0 To FileCount(strInputFolder, strfilter) + 1) As String    'DOESN'T COMPILE - FileCount - "Constant expression required"
      For i = LBound(arrayFiles) To UBound(arrayFiles)
        arrayFiles(i) = Dir(strInputFolder & strfilter)
      Next i
     FileList = arrayFiles()

End Function

'get file count
Function FileCount(strInputFolder As String, strfilter As String) As Long
    Dim strTemp2 As String
    Dim lngCount As Long
    Dim MyArray(10000) As String
    Dim i As Integer
    
    strTemp2 = Dir(strInputFolder & strfilter)
    Do While strTemp2 <> ""
        lngCount = lngCount + 1
        strTemp2 = Dir
        MyArray(i) = strTemp2        ' DOESN'T COMPILE!!!!
        Debug.Print (MyArray(i))
        i = i + 1
    Loop
    
    FileCount = lngCount

End Function

Open in new window

0
 
dlmilleCommented:
You're almost there with the code you created.  You don't really need the filecount, you need to load the dynamic array.

Note the REdim Preserve, and the calls  strtemp=Dir.

Slight modification and you're good to go.  Just tested with the calling routine called "testgetFilesFromFolderIntoArray()" sub:

 
' File Folder Search
 ' Returns a string listing files found in strFolder
  Function FileList(strInputFolder As String, Optional strfilter As String = "*.*") As Variant

    Dim strHolder As String
    Dim i As Integer
    Dim strTemp As String
    Dim strTemp2 As String
    Dim arrayFiles() As String
    

    If Right$(strInputFolder, 1) <> "\" Then strInputFolder = strInputFolder & "\"
    strTemp = Dir(strInputFolder & strfilter)
    ' make sure there are files in the folder
    If strTemp = "" Then
        MsgBox "The folder is empty.", vbCritical, "File Search"
        Exit Function
    End If

    ' count number of files
    'Call filecount(strInputFolder, strfilter)

   'load filenames into array
      
      i = 0
      Do While strTemp <> ""
        ReDim Preserve arrayFiles(i) As String
        arrayFiles(i) = strTemp
        i = i + 1
        strTemp = Dir
      Loop
      
     FileList = arrayFiles

End Function
Sub testgetFilesFromFolderIntoArray()
Dim arrayOfFiles() As String

    arrayOfFiles = FileList(ActiveWorkbook.Path, "*.*")
    
End Sub

Open in new window


Enjoy!

Dave
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
dlmilleCommented:
Redim Preserve arrayFiles(i) as String - adds one more to the dimension; the PRESERVE piece keeps what's already there without erasing it.

the count of files, after the loop, if you're interested, is:

i-1

or

UBOUND(arrayFiles)

The result, populates an array in the calling routine with the results - note the dimension of that -> arrayOfFiles() as as string...

Cheers,

Dave
0
 
dlmilleCommented:
One final finishing touch.

Click the button in sheet 1 which calls the macro for the file list, and pastes the result into Column A using this function:

 
Sub testgetFilesFromFolderIntoArray()
Dim arrayOfFiles() As String

    arrayOfFiles = FileList(ActiveWorkbook.Path, "*.*")
    
    Range("A1").Resize(UBound(arrayOfFiles), 1).Value = Application.Transpose(arrayOfFiles)
    
End Sub

Open in new window


See attached file:

Enjoy!

Dave
Demo-File-Arrays-r1.xls
0
 
LambelAuthor Commented:
Thanks, Dave.  That worked perfect.

Lynn
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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