PDA

View Full Version : Browse to a folder and extract all workbook names to a sheet in a workbook



mduff
05-09-2013, 07:40 PM
Hi,

I am working with this code but I have noticed it pulls in the names of all files in the directory even if they are not excel files or CSV.

Can any one help me adjust it or have any new code that I can use to browse to a folder and extract Only excel files or CSV names to a sheet in a workbook? Also if it's not asking too much to put some error trapping if they try to load a folder that has no excel files to give a MsgBox and exit the sub

thanks in advance

Sub CopyCurrentRegion()
Dim xRow As Long, Count As Long
Dim xDirect$, xFname$, InitialFoldr$
Dim Rng1 As Range, Rng2 As Range, Rng3 As Range
Dim x As String
Workbooks("CIGNA.xlsm").Sheets("DATA").Cells.Select
Selection.ClearContents
InitialFoldr$ = Environ("userprofile") & "\desktop\"
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$ <> ""
Workbooks("CIGNA.xlsm").Sheets("DATA").Cells(1, 1).Offset(xRow) = xFname$
xRow = xRow + 1
xFname$ = Dir
Loop
End If
End With
End Sub