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

Question

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

It's kind of confusing to edit your question to incorporate the answer, as at that point, your code does what you're asking for help on. — David-W-Fenton, Jan 29 '10 at 19:46

Fionnuala · Accepted Answer · 2013-01-15T10:38:23.197

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

score 1 · Answer 2 · answered Dec 08 '12 at 05:10

I needed to select a single text file... this is what I did... it worked fine.

' Get the File
'----------------------------------------------------------
Dim dialog As Object
Dim pickedfile As Boolean
Dim myfile As String
Set dialog = Application.FileDialog(msoFileDialogFilePicker)
With dialog
    .AllowMultiSelect = False
    .Title = "Please pick the file to convert."
    .Filters.Clear
    .Filters.Add "Text Files", "*.TXT"
    .Filters.Add "All Files", "*.*"
    pickedfile = False
    pickedfile = .Show
    If pickedfile Then
    myfile = .SelectedItems.Item(1)
    End If
End With
'----------------------------------------------------------

Additionally... you can replace the dialog type with...

Set dialog = Application.FileDialog(msoFileDialogOpen)

and it worked equally well.

score 0 · Answer 3 · edited Mar 13 '13 at 15:27

0

Private Sub Command135_Click()

Dim dialog As Object
Dim pickedfile As Boolean
Dim myfile As String
Set dialog = Application.FileDialog(1)
With dialog
    .AllowMultiSelect = False
    .Title = "Please pick the file to convert."
    .Filters.Clear
    .Filters.Add "Picture Files", "*.Jpg"
    .Filters.Add "All Files", "*.*"
    pickedfile = False
    pickedfile = .Show
    If pickedfile Then
    myfile = .SelectedItems.Item(1)
    End If
End With

Me.Form.Picture = myfile
End Sub


Command_135=Button Name
Me.Form.Picture = "The Control Name"

edited Mar 13 '13 at 15:27

Rakesh

3,370
2
23
41

answered Mar 13 '13 at 15:04

NAsif

1

Give a brief description of the code that you have posted so that it is helpful to others. – Rakesh Mar 13 '13 at 15:26

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

3 Answers3

Linked