Search This Blog

Friday 27 January 2012

CODE FOR GETTING THE FILE PATH OF ALL THE FILES IN A FOLDER INTO YOUR EXCELSHEET



Sub get_file_path()
    Dim xRow As Long
    Dim xDirect$, xFname$, InitialFoldr$
    Sheets(1).Select
    InitialFoldr$ = "D:\"       '<<< Startup folder to begin searching from
    Range("A2").Select
    With Application.FileDialog(msoFileDialogFolderPicker)
        .InitialFileName = Application.DefaultFilePath & "\"
        .Title = "Please select a folder to list Files from"
        .InitialFileName = InitialFoldr$
        .Show
        If .SelectedItems.Count <> 0 Then
            xDirect$ = .SelectedItems(1) & "\"
            xFname$ = Dir(xDirect$, 7)
            Do While xFname$ <> ""
                ActiveCell.Offset(xRow) = xDirect$ & xFname$
                xRow = xRow + 1
                xFname$ = Dir
            Loop
        End If
    End With
    End Sub

No comments:

Post a Comment