Excel VBA- File Dialog

EXCEL VBA TO EXTRACT FULL PATH & FILENAME OF FILES OF A FOLDER

METHOD1:

Sub pFilesPathAndFilesName1()

    Dim strPath As String

    Dim objFileD As Object

    Dim lngFileCnt As Long

    Dim strArrPath()

    Dim strArrName()

    Dim lngR As Long

   

    Set objFileD = Application.FileDialog(msoFileDialogFilePicker)

   

    With objFileD

        .AllowMultiSelect = 1

        .Show

        For lngFileCnt = 1 To .SelectedItems.Count

        ReDim Preserve strArrPath(lngFileCnt)

        ReDim Preserve strArrName(lngFileCnt)

        strArrPath(lngFileCnt - 1) = .SelectedItems(lngFileCnt)

        strArrName(lngFileCnt - 1) = Split(strArrPath(lngFileCnt - 1), "\")(UBound(Split(strArrPath(lngFileCnt - 1), "\")))

        Next

    End With

    Range("A:B").ClearContents

    Range("A1").Resize(UBound(strArrPath) + 1, 1) = Application.Transpose(strArrPath)

    Range("B1").Resize(UBound(strArrName) + 1, 1) = Application.Transpose(strArrName)

   

End Sub

METHOD1 WITH SLIGHT MODIFICATION

Sub pFilesPathAndFilesName2()

    Dim strPath As String

    Dim objFileD As Object

    Dim lngFileCnt As Long

    Dim strArr()

    Dim lngR As Long

   

    Set objFileD = Application.FileDialog(msoFileDialogFilePicker)

   

    With objFileD

        .AllowMultiSelect = 1

        .Show

        For lngFileCnt = 1 To .SelectedItems.Count

        ReDim Preserve strArr(lngFileCnt)

        strArr(lngFileCnt - 1) = .SelectedItems(lngFileCnt)

        Next

    End With

    Range("A:A").ClearContents

    Range("A1").Resize(UBound(strArr) + 1, 1) = Application.Transpose(strArr)

    For lngR = 1 To UBound(strArr)

    Range("B" & lngR) = Split(Range("A" & lngR), "\")(UBound(Split(Range("A" & lngR), "\")))

    Next

   

End Sub