Is There a Common Dialog Box I Can Use to Capture a Path?

[JerryDal]

I tried this code in Excel 2003 and it tries to open a file I select:
dlgAnswer = Application.Dialogs(xlDialogOpen).Show

The code is executed from a command button on a worksheet.
What I want to do is navigate to a folder, click on any type of file, and then parse out the path to store it in a cell.

Is there a common dialog box that only retrieves the path and file ID in VBA (I will remove the file ID), and not try to open the file?

 

[SkipVought]

Hi,

Check out Application.GetOpenFileName()

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[JerryDal]

Thanks for the Excel tip on Application.GetOpenFileName(). I tried it and it does capture the full path along with the name of a file clicked on.
I found code that does what my prompt says: "Choose a folder".

Code:
Function BrowseFolder(Title As String, _
Optional InitialFolder As String = vbNullString, _
Optional InitialView As Office.MsoFileDialogView = _
msoFileDialogViewList) As String
Dim V As Variant
Dim InitFolder As String
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = Title
.InitialView = InitialView
If Len(InitialFolder) > 0 Then
If Dir(InitialFolder, vbDirectory) <> vbNullString Then
InitFolder = InitialFolder
If Right(InitFolder, 1) <> "\" Then
InitFolder = InitFolder & "\"
End If
.InitialFileName = InitFolder
End If
End If
.Show
On Error Resume Next
Err.Clear

'added this IF test because ".SelectedItems(1)" caused error on Windows XP PC when no item was selected
If .SelectedItems.Count > 0 Then 'added this test
V = .SelectedItems(1)
If Err.Number <> 0 Then
V = vbNullString
End If
End If
End With
BrowseFolder = CStr(V)
End Function

RESOLVED