FileDialogPicker

josros60

Well-known Member
Joined
Jun 27, 2010
Messages
524
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.


VBA Code:
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,
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,725
Office Version
  1. 2010
Platform
  1. Windows
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
VBA Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,098
Messages
5,622,678
Members
415,921
Latest member
ExcelNoob28

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top