| Home |
| Search |
| Today's Posts |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dir() function to return either List of files or folders
Thanks! It works PERFECT!
"Dave Peterson" wrote: This worked ok for me. I added a sort and removed the directory only part. Option Explicit Public Function GetFileListArray(ByVal myPath As String, _ Optional ByVal Filter As String = "*.*") As Variant Dim DirectoryFiles() As String Dim strFileName As String Dim CountOfFiles As Long Dim CountOfCells As Long Dim iCtr As Long Dim jCtr As Long Dim temp As String 'only one row or only one column With Application.Caller If .Columns.Count 1 _ And .Rows.Count 1 Then GetFileListArray = CVErr(xlErrRef) Exit Function End If CountOfCells = .Cells.Count End With If Right(myPath, 1) < "\" Then myPath = myPath & "\" End If CountOfFiles = 0 strFileName = Dir(myPath & Filter) Do While strFileName < "" If GetAttr(myPath & strFileName) = vbDirectory Then 'do nothing Else CountOfFiles = CountOfFiles + 1 ReDim Preserve DirectoryFiles(1 To CountOfFiles) DirectoryFiles(CountOfFiles) = strFileName End If strFileName = Dir() Loop If CountOfFiles = 0 Then GetFileListArray = CVErr(xlErrRef) Else For iCtr = LBound(DirectoryFiles) To UBound(DirectoryFiles) - 1 For jCtr = iCtr + 1 To UBound(DirectoryFiles) If LCase(DirectoryFiles(iCtr)) LCase(DirectoryFiles(jCtr)) _ Then temp = DirectoryFiles(iCtr) DirectoryFiles(iCtr) = DirectoryFiles(jCtr) DirectoryFiles(jCtr) = temp End If Next jCtr Next iCtr ReDim Preserve DirectoryFiles(1 To CountOfCells) For iCtr = CountOfFiles + 1 To UBound(DirectoryFiles) DirectoryFiles(iCtr) = "" Next iCtr If Application.Caller.Columns.Count = 1 Then GetFileListArray = Application.Transpose(DirectoryFiles) Else GetFileListArray = DirectoryFiles End If End If End Function Myriam wrote: Thanks. I now have folder names in the cells! I encountered 3 other problems: 1. The list is not in the same order as the directory 2. There is a dot (.) on the first cell and two dots (..) on the second cell 3. There is #N/A on the remaining cells (the extra space in the range) What might be causing #1 and #2, I think I can fix #3. And, what i need are the file names, not the subfolders, but I have not been able to make R Avery's first code work. "Myrna Larson" wrote: Looking at this section of Dave's code, If CountOfFiles = 0 _ Or CountOfFiles (Application.Caller.Columns.Count _ * Application.Caller.Rows.Count) Then GetFileListArray = CVErr(xlErrRef) you will get a reference error if there are no directories or the number of directories exceeds the number of cells you selected when you entered the formula on the worksheet. Note: you should select A1:A30, type the formula in A1, then press CTRL+SHIFT+ENTER. You don't type it in A1 and copy it down, in case that's what you did. On Sat, 17 Jul 2004 10:44:01 -0700, Myriam wrote: I tried it and I get #REF. I copied your code in Module 1 in Sheet 1, A1:A30 I copied your formula with Ctrl+Shift+Enter, the computer placed the {} The result was #REF. What am I doing wrong? Thanks! "Dave Peterson" wrote: If you want to use it from a worksheet 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" wrote in message ... 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 -- Dave Peterson |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| macro to list folders only, not files | Excel Worksheet Functions | |||
| .tmp files filling up my folders | Excel Discussion (Misc queries) | |||
| undelete files or folders? | Excel Discussion (Misc queries) | |||
| Can anyone help me Create Excel list of files in windows folders | Excel Worksheet Functions | |||
| links to same files in different folders | Excel Worksheet Functions | |||
Threaded Mode
