vba - ms access browse for file and get file name and path

Question

I am using ms access and i want to add a button to browse for a file, get the name of the file and its path . i then want to store the file path and file name in 2 separate variables. The code i have so far is below and at the moment i can browse for a file and get the name of the file only. Can anyone help me add to my code to get the file path and to store both the file name and file path in separate variables.

Private Sub Command7_Click()

Dim f As Object

Set f = Application.FileDialog(3)

f.AllowMultiSelect = True

If f.Show Then
    For i = 1 To f.SelectedItems.Count
        MsgBox Filename(f.SelectedItems(i))
    Next
End If

End Sub


Public Function Filename(ByVal strPath As String) As String

If Right$(strPath, 1) <> "" And Len(strPath) > 0 Then
    Filename = Filename(Left$(strPath, Len(strPath) - 1)) + Right$(strPath, 1)

End If

End Function

Answer 1

You are passing the full path to your function, so you can get the path from that. For example:

Public Function Filename(ByVal strPath As String, sPath) As String
    sPath = Left(strPath, InStrRev(strPath, ""))
    Filename = Mid(strPath, InStrRev(strPath, "") + 1)
End Function

Called by, say:

    sFile = Filename(f.SelectedItems(i), sPath)
    MsgBox sPath & "---" & sFile

In full

Private Sub Command7_Click()

Dim f As Object

Set f = Application.FileDialog(3)

f.AllowMultiSelect = True

If f.Show Then
    For i = 1 To f.SelectedItems.Count
        sFile = Filename(f.SelectedItems(i), sPath)
        MsgBox sPath & "---" & sFile
    Next
End If

End Sub


Public Function Filename(ByVal strPath As String, sPath) As String
    sPath = Left(strPath, InStrRev(strPath, ""))
    Filename = Mid(strPath, InStrRev(strPath, "") + 1)
End Function