VBA – selecting a folder and referencing it as the path for a separate code

Question 1

I’m able to use this code to select a folder:

Sub ChooseFolder()
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:
GetFolder = sItem
Set fldr = Nothing
End Sub

I also have this code, that works when the folder path is hardcoded. Basically, it gives me a list of file names and file paths that I use later in a separate section. Currently I have the hardcoded folder path commented out and I’m trying to use the above code to select the folder each time so that it is more user friendly.

Private Sub btn_LeaveReport()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim i As Integer

'Create an instance of the FileSystemObject
Set objFSO = CreateObject("Scripting.FileSystemObject")

'Get the folder object
'Set objFolder = objFSO.GetFolder("D:\Administration\Time Sheets")
Set objFolder = objFSO.GetFolder(ChooseFolder)
i = 3

'loops through each file in the directory and prints their names and path
For Each objFile In objFolder.Files
    'print file name
    Cells(i + 1, 2) = objFile.Name
    'print file path
    Cells(i + 1, 3) = objFile.Path
    i = i + 1
Next objFile
End Sub

However, I’m not sure how to get the two different code sets to work together. I’m guessing the only part I need to change is this:

Set objFolder = objFSO.GetFolder(ChooseFolder)

I have it as ChooseFolder which is the sub above for now but that is clearly not the way to go about it. I tried it with sItem as well but that doesn’t seem to work.

Question 2

Just to build on my comment with a better explanation, you have defined ChooseFolder as a Sub. Subs do not return values. However, you’re using it as a Function when you do this:

Set objFolder = objFSO.GetFolder(ChooseFolder)

because you’re passing the result of running ChooseFolder to the FSO’s GetFolder function.

What you need to do is declare ChooseFolder as a Function.

Basically, replace your ChooseFolder Sub with this:

Function ChooseFolder() As String
    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:
    ChooseFolder = sItem
    Set fldr = Nothing
End Function

and it should then do what you expect. The rest of your code is fine.

Question 3

Make ChooseFolder() into a function and then reference it:

Public Function ChooseFolder()
    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:
    ChooseFolder = sItem
    Set fldr = Nothing
End Function


Private Sub btn_LeaveReport()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim i As Integer
Dim sFldr As String

'Create an instance of the FileSystemObject
Set objFSO = CreateObject("Scripting.FileSystemObject")

sFldr = ChooseFolder()
Set objFolder = objFSO.GetFolder(sFldr)
i = 3

'loops through each file in the directory and prints their names and path
For Each objFile In objFolder.Files
    'print file name
    Cells(i + 1, 2) = objFile.Name
    'print file path
    Cells(i + 1, 3) = objFile.Path
    i = i + 1
Next objFile
End Sub

Leave a Comment Cancel reply