How can I add a folder picker option to this code?

[Sheesha1984]

Hi, I'll preface by saying I didn't create this code so I'm open to any changes. The code is meant to create a list of files in a folder. It gives me a list of files with the extension on column A. I would like to be able to choose what folder to use each time instead of having to replace the code or work out of one particular folder. I know basically nothing about excel macros/VBA but I'm trying to learn. Can someone tell me how to replace the static folder location and create a prompt to choose. Thanks!

Sub LoopThroughFiles()

Dim oFSO As Object

Dim oFolder As Object

Dim oFile As Object

Dim i As Integer

Set oFSO = CreateObject("Scripting.FileSystemObject")

Set oFolder = oFSO.GetFolder("C:\VBA testing")

For Each oFile In oFolder.Files

Cells(i + 1, 1) = oFile.Name

i = i + 1

Next oFile

End Sub

 

[JEC]

Try this macro

Sub jec()
 Dim ar(), it As Variant, x As Long
 With Application.FileDialog(4)
   If .Show Then
     For Each it In CreateObject("scripting.filesystemobject").getfolder(.SelectedItems(1)).Files
       ReDim Preserve ar(x)
       ar(x) = it.Name
       x = x + 1
     Next
   End If
 End With
 Sheets(1).Cells(1, 1).Resize(x) = Application.Transpose(ar)
End Sub

 

[Sheesha1984]

Sub jec() Dim ar(), it As Variant, x As Long With Application.FileDialog(4) If .Show Then For Each it In CreateObject("scripting.filesystemobject").getfolder(.SelectedItems(1)).Files ReDim Preserve ar(x) ar(x) = it.Name x = x + 1 Next End If End With Sheets(1).Cells(1, 1).Resize(x) = Application.Transpose(ar) End Sub

Thank you! That worked great. I appreciate the help.

 

[JEC]

You're welcome!