Re: Dir() function to return either List of files or folders

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Dave Peterson (ec35720_at_msn.com)
Date: 07/17/04 

Date: Sat, 17 Jul 2004 06:20:42 -0500

If you want to use it from a work*** cell:

Option Explicit
Public Function GetFileListArray(ByVal Path As String, _
                           Optional ByVal Filter As String = "*.*") As Variant
    Dim DirectoryFiles() As String
    Dim strFileName As String
    Dim CountOfFiles As Long
    
    CountOfFiles = 0
    strFileName = Dir(Path & Filter, vbDirectory)
    Do While strFileName <> ""
         If GetAttr(Path & strFileName) = vbDirectory Then
         ReDim Preserve DirectoryFiles(CountOfFiles)
              DirectoryFiles(CountOfFiles) = strFileName
              CountOfFiles = CountOfFiles + 1
         End If
         strFileName = Dir()
    Loop
    
    If CountOfFiles = 0 _
      Or CountOfFiles > (Application.Caller.Columns.Count _
                          * Application.Caller.Rows.Count) Then
       GetFileListArray = CVErr(xlErrRef)
    Else
        If Application.Caller.Columns.Count = 1 Then
            GetFileListArray = Application.Transpose(DirectoryFiles)
        Else
            GetFileListArray = DirectoryFiles
        End If
    End If
    
End Function

Select your range (single column or single row). Make sure it's big enough to
hold all the values you expect.

Then type your formula:
=GetFileListArray("C:\my documents\excel\")

Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap
curly brackets {} around your formula. (don't type them yourself.)

And Transpose is limited to 5461 elements in earlier versions of excel (before
xl2002).

Ian Ripsher wrote:
>
> "R Avery" <ravery74@yahoo.co.uk> wrote in message
> news:OsTGLM4aEHA.1644@tk2msftngp13.phx.gbl...
> > I've created the following function to return a list of files given a
> > directory path. How would I modify it to return only folders? I have
> > tried to supply vbDirectory as an argument to the Dir function, but that
> > returns both folders and files. How do I make it return only folders?
> >
> >
> > Public Function GetFileListArray(ByVal Path As String, Optional ByVal
> > Filter As String = "*.*") As String()
> > Dim DirectoryFiles() As String
> > Dim strFileName As String
> >
> > strFileName = Dir(Path & Filter)
> > Do While strFileName <> ""
> > If strFileName <> "" Then
> > ReDim Preserve DirectoryFiles(Count)
> > DirectoryFiles(Count) = strFileName
> > Count = Count + 1
> > End If
> > strFileName = Dir()
> > Loop
> >
> > GetFileListArray = DirectoryFiles
> > End Function
>
> I've tried both your code and John Green's for this function, and always get
> #VALUE!. This is presumably because I'm using it in a workshet cell - can it
> only be called from a Sub? 

-- 
Dave Peterson
ec35720@msn.com
Quantcast