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

From: Ian Ripsher (ripsher__at__btinternet.com)
Date: 07/17/04 

Date: Sat, 17 Jul 2004 17:09:20 +0100

"Dave Peterson" <ec35720@msn.com> wrote in message
news:40F90B8A.543777A2@msn.com...
> 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).

Thanks. I thought it would have something to do with array functions (Ctrl
Shift Enter, etc).