FileDialogPicker

[josros60]

Hi

how can i modify the below code to have dialog box to select folder instead of folder path in the code so user can select the folder when prompt.

Sub ListFiles2()
Dim fileList() As String
Dim fName As String
Dim fPath As String
Dim i As Integer
Dim startrow As Integer
Dim ws As Worksheet
Dim filetype  As String
Dim sFolder As String

'=======================================================
[B][COLOR=rgb(184, 49, 47)]fPath = "C:\Weekly AP\"[/COLOR][/B]
filetype = "*"
 
Set ws = Worksheets("LIST_FILE NAMES")
startrow = 2    'starting row for the data
'========================================================

fName = Dir(fPath & "*." & filetype)
While fName <> ""
    i = i + 1
    ReDim Preserve fileList(1 To i)
    fileList(i) = fName
    fName = Dir()
Wend
If i = 0 Then
    MsgBox "No files found"
    Exit Sub
End If
    
For i = 1 To UBound(fileList)
    ws.Range("A" & i + startrow).Value = fileList(i)
Next
Columns(1).AutoFit

End Sub

thank you,

 

[offthelip]

I am not quite sure what you want, but this code will allow you to select a folder. You need to set a reference to "Microsoft Scripting Runtime" in order to get the file dialog to work

Sub browsef()
Dim pathn As String

pathn = ActiveWorkbook.Path
FolderName = GetUsrFolder(pathn)
MsgBox (FolderName)


End Sub

Function GetUsrFolder(strPath As String) As String
' Set a reference to "Microsoft Scripting Runtime"
Dim fldr As FileDialog
Dim sItem As String
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
    .Title = "Select a Folder"
    .AllowMultiSelect = False
    .InitialFileName = strPath
    If .Show <> -1 Then GoTo NextCode
    sItem = .SelectedItems(1)
End With
NextCode:
GetUsrFolder = sItem
Set fldr = Nothing
End Function

Just run the browsef macro to see how it works.