List out the file names present in the folder using excel macro

Hi All,

Im having n number of files in the folder and all the files are same but having diffrent data and diffrent name.I want to pull the data's from N number of files to master excel file.I m able to pull the data from all the files.Here i want to list out the file names when i select the data from multiple files.

Im having the seperate code for pull the data and list out the file names.But i want to merge the code to perform action at a single shot.Consolidated data base work book having more sheets..

Any one can take a look and help me out.

below is the code for listing file names present in the folder:

Option Explicit
 Sub GetFileNames()
 Dim xRow As Long
 Dim xDirect$, xFname$, InitialFoldr$
 InitialFoldr$ = "C:\" '<<< Startup folder to begin searching from
 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) = xFname$
 xRow = xRow + 1
 xFname$ = Dir
 Loop
 End If
 End With
 End Sub

And below is the code for pull the data from more excel files

Option Explicit



Private Sub cmdImport_Click()
Dim SrcWB As Workbook
Dim DBWB As Workbook
Dim SrcRng As Range
Dim SrcCell As Range
Dim NextRow As Long
Dim A As Long
Dim aCol As Long

    Set DBWB = ThisWorkbook

    'Declare a variable as a FileDialog object.
    Dim fd As FileDialog

    'Create a FileDialog object as a File Picker dialog box.
    Set fd = Application.FileDialog(msoFileDialogFilePicker)

    'Declare a variable to contain the path
    'of each selected item. Even though the path is aString,
    'the variable must be a Variant because For Each...Next
    'routines only work with Variants and Objects.
    Dim vrtSelectedItem As Variant

    'Use a With...End With block to reference the FileDialog object.
    With fd

        'Use the Show method to display the File Picker dialog box and return the user's action.
        'The user pressed the button.
        If .Show = -1 Then

            'Step through each string in the FileDialogSelectedItems collection.
           NextRow = 3
           aCol = 3
            For Each vrtSelectedItem In .SelectedItems
                With DBWB.Worksheets("Defect")
                    'NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
                    
                    
                        If vrtSelectedItem <> ThisWorkbook.Name Then
                            Me.Label1.Caption = vrtSelectedItem
                            Set SrcWB = Workbooks.Open(vrtSelectedItem)

                            For Each SrcCell In SrcWB.Worksheets("Raw Data Sheet").Range("C37:I37")
                                aCol = aCol + 1
                                DBWB.Worksheets("Defect").Cells(NextRow, aCol) = SrcCell
                            Next
                            NextRow = NextRow + 1
                            aCol = 3
                            SrcWB.Close

                        End If
                        Me.Label1.Caption = vrtSelectedItem
                End With

            Next
           NextRow = 3
           aCol = 3
            For Each vrtSelectedItem In .SelectedItems
                With DBWB.Worksheets("Schedule")
                    'NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
                    
                    
                        If vrtSelectedItem <> ThisWorkbook.Name Then
                            Me.Label1.Caption = vrtSelectedItem
                            Set SrcWB = Workbooks.Open(vrtSelectedItem)

                            For Each SrcCell In SrcWB.Worksheets("Raw Data Sheet").Range("C6,I6")
                                aCol = aCol + 1
                                DBWB.Worksheets("Schedule").Cells(NextRow, aCol) = SrcCell
                            Next
                            NextRow = NextRow + 1
                            aCol = 3
                            SrcWB.Close

                        End If
                        Me.Label1.Caption = vrtSelectedItem
                End With

            Next
                       
            End If
        End With
        Unload Me
    End Sub



Private Sub CommandButton2_Click()
Unload Me
End Sub

Private Sub Label1_Click()

End Sub

Private Sub UserForm_Click()

End Sub

Private Sub UserForm_Initialize()



End Sub

Thanks ,
Lakshmanan