Page tree

Welcome to FreeSoftwareServers Confluence Wiki

Skip to end of metadata
Go to start of metadata
Public Sub ListAllFilesInFolder()
    Dim Desktop10 As String
    Dim objFSO As Object
    Dim objFolder As Object
    Dim objFile As Object
    Dim ws As Worksheet
    'Desktop10 = CreateObject("WScript.Shell").SpecialFolders("Desktop") & Application.PathSeparator

    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set ws = ActiveSheet
     'Get the folder object associated with the directory
    Dim FolderPath
    FolderPath = BrowseForFolder()
    FolderPath = FolderPath & "\"
    Set objFolder = objFSO.GetFolder(FolderPath)
    'Insert Column if Col A is NOT Empty
    Application.CutCopyMode = False
    If IsRngEmpty(Range("A:A")) = False Then
     On Error GoTo ErrorHandler
     ActiveSheet.Columns("A").Insert Shift:=xlToRight
    GoTo Continue
    WorksheetCreateDelIfExists ("File_List")

    End If
    Application.CutCopyMode = True
    'Loop through the Files collection
    Dim i As Integer
    i = 1
    For Each objFile In objFolder.Files
        ws.Cells(i, 1).Value = Left(objFile.Name, InStrRev(objFile.Name, ".") - 1)
        i = i + 1
     'Clean up!
    Set objFolder = Nothing
    Set objFile = Nothing
    Set objFSO = Nothing

End Sub

  • No labels