computing
  • 2

Folder Name Extraction

  • 2

I have about 1,000 data discs and I want to make a list of their contents. The main item would be to extract the folder names from each disc using a Macro in Excel. It is possible that may be a file mixed in with these folders (e.g. there are three folders and one file on the root disc). Does anyone have a way to easily extract these names? A side would be to also include any sub-folders and files, but not necessary.

Share

1 Answer

  1. Hi,

    This solution allows you to select files using the Open File dialog, from inside Excel.
    All files selected are appended to any data already in Column A of Worksheet ‘Sheet1’
    The Drive and Path are placed in column A
    The filename is placed in Column B.

    When you run the Macro again, the next selection of files is added to the existing paths/filenames.

    Create a Workbook named Getfilename.xls
    On Sheet1 create a Command Button from the Control Toolbox. If this isn’t visible, right click on an existing toolbar and check the Control Toolbox.
    Select the button Icon and draw a button
    Right-click the button and select Command Button – Edit and change the name to something useful !
    Right-click the button again and select View Code
    In the code window that opens enter this:

    Option Explicit
    
    Private Sub CommandButton1_Click()
    Dim rngSave As Range
    Dim lngCount As Long
    Dim strPathFile As String
    Dim strFname As String
    Dim intLastDiv As Integer
    Dim n As Integer
    
    On Error GoTo ErrHnd
    
    With ActiveSheet
        'find the last used cell in column A
        Set rngSave = .Range("A65534").End(xlUp)
        
        ' Open the file dialog
        With Application.FileDialog(msoFileDialogFilePicker)
            .AllowMultiSelect = True
            .Show
            'Copy all path/filenames into column A
            For lngCount = 1 To .SelectedItems.Count
                strPathFile = .SelectedItems(lngCount)
                'find last "/" in path/filename
                intLastDiv = 0
                For n = 1 To Len(strPathFile)
                    If Mid(strPathFile, n, 1) = "" Then
                        intLastDiv = n
                    End If
                Next n
                rngSave.Offset(lngCount - 1, 0) = strPathFile
                'split filename from path
                'save path in column A
                rngSave.Offset(lngCount - 1, 0).Value = Left(strPathFile, intLastDiv)
                'save filename in column B
                rngSave.Offset(lngCount - 1, 1).Value = Right(strPathFile, Len(strPathFile) - intLastDiv)
            Next lngCount
        End With
        'set column widths to fit
        .Range("A1:B" & Format(rngSave.Row + lngCount, "##0")).Columns.AutoFit
    End With
    Exit Sub
    
    'error handler
    ErrHnd:
    Err.Clear
    End Sub

    Note that Private Sub CommandButton1_Click() and End Sub will already be there, so don’t duplicate them
    Option Explicit goes before Private Sub CommandButton1_Click()

    Use Alt +f11 to return to the Excel Window. From the Control Toolbox toolbar select the Exit Design Mode icon, and close the toolbar.

    The Command button will now run the macro.

    This routine worked on hard drive partitions, and a CD drive and also worked on a USB connected floppy as well as on a network drive.

    Regards

    • 0