CODEWITHSUNDEEP
vbaexcelshowfiledialog
Ionut Mihaila
Ionut Mihaila

Reputation: 1

VBA FileDialog.Show restarts sub

I'm trying to get the path for a folder using the filedialog.show function.

The issue I am facing is:

What could be the problem? 

[...]

Dim fpath As Variant
Dim fldr As Object
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
fldr.Title = "Select a Folder"
fldr.AllowMultiSelect = False
fldr.InitialFileName = Application.DefaultFilePath
If fldr.Show = -1 Then
    fpath = fldr.SelectedItems(1)
Else
    GoTo NextCode
End If
NextCode: 
set fldr = Nothing

[...]

Upvotes: 0

Views: 284

Answers (1)

Shai Rado
Shai Rado

Reputation: 33682

It works, you are just not using it to display the path result (or return a String value from this Sub).

Change your code:

If fldr.Show = -1 Then
    fpath = fldr.SelectedItems(1)
Else
    GoTo NextCode
End If

NextCode: 
set fldr = Nothing

To:

If fldr.Show = -1 Then
    fpath = fldr.SelectedItems(1)
    MsgBox fpath ' <-- for DEBUG
End If

Set fldr = Nothing

If you want to use your code as a Function that returns the path of the selected folder, use the code below:

Function GetFolderPath() As String

Dim fpath As Variant
Dim fldr As Object

Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
    .Title = "Select a Folder"
    .AllowMultiSelect = False
    .InitialFileName = Application.DefaultFilePath

    If .Show = -1 Then
        GetFolderPath = .SelectedItems(1)
    End If
End With    
Set fldr = Nothing

End Function

And the Sub code to test it:

Sub Test()

Dim FolderPath As String

FolderPath = GetFolderPath
MsgBox FolderPath

End Sub

Upvotes: 1

Related Questions