Results 1 to 6 of 6

List excel files in current directory....

This is a discussion on List excel files in current directory.... within the Excel Questions forums, part of the Question Forums category; I'm trying to write a macro that will display a list of all excel files within the current directory, any ...

  1. #1
    New Member
    Join Date
    Jul 2002
    Posts
    49

    Default

    I'm trying to write a macro that will display a list of all excel files within the current directory, any ideas?

  2. #2
    Board Regular Terry_Orange's Avatar
    Join Date
    Jul 2002
    Posts
    133

    Default

    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.


  3. #3
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582

    Default

    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

  4. #4
    Board Regular XL-Dennis's Avatar
    Join Date
    Jul 2002
    Location
    Ístersund, Sweden
    Posts
    1,922

    Default



    [ This Message was edited by: XL-Dennis on 2002-11-14 18:39 ]

  5. #5
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582

    Default

    Hmmm, well spotted Dennis. I used

    .LookIn = strPath Twice!

    The first one was supposed to be .NewSearch



  6. #6
    Board Regular
    Join Date
    Oct 2002
    Posts
    143

    Default

    Anyone know how to alter this code to give just the filename, istead of the path & filename?

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com