How do I get a single file name out of a File Dialog object in VBA (for MS Access 2007)?

ms-access, openfiledialog, vba

Solution

You want:

OpenDialog.SelectedItems.Item(1)

In place of:

OpenDialog.InitialFileName

As you have not allowed multiselect.

So:

''Reference Microsoft Office x.x Object Library
Dim openDialog As Office.FileDialog

Set openDialog = Application.FileDialog(msoFileDialogFilePicker)
openDialog.Filters.Clear
openDialog.Filters.Add "JPEG Files", "*.jpg"

If openDialog.Show Then
    ''SelectedItems is not zero based

    ''Do not use .Text property in MS Access except
    ''in special cases, then you will not have to set focus
    ''txtImageName.SetFocus

    txtImageName = openDialog.SelectedItems(1)
End If

If AllowMultiSelect is used, you need to iterate through SelectedItems

''Reference Microsoft Office x.x Object Library
Dim openDialog As Office.FileDialog
Dim i As Integer

Set openDialog = Application.FileDialog(msoFileDialogFilePicker)
'Use ctl or shift + click to select more than one file
openDialog.AllowMultiSelect = True
openDialog.Filters.Clear
openDialog.Filters.Add "JPEG Files", "*.jpg"

If openDialog.Show Then
    For i = 1 To openDialog.SelectedItems.Count
        Imagelst = Imagelst & ";" & openDialog.SelectedItems(i)
    Next
End If

Problem

How do I change my code to get the file name instead of the directory name? `openDialog.InitialFilename` gives me the directory name. `openDialog.FileName` gives me the error "Method or data member not found". ``` Private Sub btnEditPhoto_Click() If (txtImageName > "") Then Application.FollowHyperlink txtImageName Else Dim openDialog As Office.FileDialog Set openDialog = Application.FileDialog(msoFileDialogFilePicker) openDialog.Filters.Clear openDialog.Filters.Add "JPEG Files", "*.jpg" Dim pickedFile As Boolean pickedFile = openDialog.Show If pickedFile Then txtImageName.SetFocus txtImageName.Text = openDialog.InitialFileName End If End If End Sub ```

Original source