List excel files in current directory....

UKPhil

New Member
Joined
Jul 25, 2002
Messages
49
I'm trying to write a macro that will display a list of all excel files within the current directory, any ideas?
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
No problems ! You will need two / three pieces of code shown below.

Insert this function.

Function GetAllFilesInDir(ByVal strDirPath As String) As Variant
' Loop through the directory specified in strDirPath and save each
' file name in an array, then return that array to the calling
' procedure.
' Return False if strDirPath is not a valid directory.
Dim strTempName As String
Dim varFiles() As Variant
Dim lngFileCount As Long

On Error GoTo GetAllFiles_Err

' Make sure that strDirPath ends with a "" character.
If Right$(strDirPath, 1) <> "" Then
strDirPath = strDirPath & ""
End If

' Make sure strDirPath is a directory.
If GetAttr(strDirPath) = vbDirectory Then
strTempName = Dir(strDirPath, vbDirectory)
Do Until Len(strTempName) = 0
' Exclude ".", "..".
If (strTempName <> ".") And (strTempName <> "..") Then
' Make sure we do not have a sub-directory name.
If (GetAttr(strDirPath & strTempName) _
And vbDirectory) <> vbDirectory Then
' Increase the size of the array
' to accommodate the found filename
' and add the filename to the array.
ReDim Preserve varFiles(lngFileCount)
varFiles(lngFileCount) = strTempName
lngFileCount = lngFileCount + 1
End If
End If
' Use the Dir function to find the next filename.
strTempName = Dir()
Loop
' Return the array of found files.
GetAllFilesInDir = varFiles
End If
GetAllFiles_End:
Exit Function
GetAllFiles_Err:
GetAllFilesInDir = False
Resume GetAllFiles_End
End Function





Then all you need is to insert into form or module you are using...

strDirName = "C:Program Filesdata"
varFileArray = GetAllFilesInDir(strDirName)


I tend to use a DropDown menu button in a form (called cb_DropDown) and so use:

With Me.cb_DropDown
.Clear
For i = 0 To UBound(varFileArray)
ExtensionStr = Right(varFileArray(i), 3)
If ExtensionStr = "txt" Then
.AddItem varFileArray(i)
Else
Debug.Print varFileArray(i)
End If
Next i
.ListIndex = 0
End With

To list all of the .txt files in the folder specified earlier. Change this to xls for excel sheets. You may also need to specify variables if you set it up that way.

TO.
 
Upvote 0
Hi Phil


Try this code on a clean sheet


Code:
Sub ListThem()
Dim i As Integer
Dim strPath As String
strPath = Workbooks(ActiveWorkbook.Name).Path

    With Application.FileSearch
       .LookIn = strPath
       .LookIn = strPath
       If .Execute > 0 Then 'Workbook exists
            For i = 1 To .FoundFiles.Count
                 Cells(i, 1) = .FoundFiles(i)
            Next i
        End If
    End With
End Sub
 
Upvote 0
Hmmm, well spotted Dennis. I used

.LookIn = strPath Twice!

The first one was supposed to be .NewSearch
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,148
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top