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

Harvell

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

Singe

Hi Remou, thanks for you reply. how can i store both the file name and file path in two separate variables , i need to use these variables to copy the file to a new folder – Harvell

If you look at the function, you will find that the path is stored in sPath and the file name is returned by Filename. – Singe

I get an error "wrong number of arguments or invalid property assignment" when i try that code – Harvell

I tested before posting. It works. What changes did you make? – Singe

i copied your code over i get "user defined type not define" and "f as Filedialog" is highlighted – Harvell

Okay, that I will admit to, just change it to object, but that declaration was not part of the original sample. – Singe

still get an error "wrong number of arguments or invalid property assignment". "sFile = Filename(f.SelectedItems(i), sPath) MsgBox sPath & "---" & sFile" seems to be the problem – Harvell

Are you sure you have this line right Public Function Filename(ByVal strPath As String, sPath) ? Are you using the code I posted or did you make changes? – Singe

Answer 2

For what you want from your click event procedure, there is no need to call a separate custom VBA function.

Private Sub Command7_Click()
    Dim f As Object
    Dim strFile As String
    Dim strFolder As String
    Dim varItem As Variant

    Set f = Application.FileDialog(3)
    f.AllowMultiSelect = True
    If f.Show Then
        For Each varItem In f.SelectedItems
            strFile = Dir(varItem)
            strFolder = Left(varItem, Len(varItem) - Len(strFile))
            MsgBox "Folder: " & strFolder & vbCrLf & _
                "File: " & strFile
        Next
    End If
    Set f = Nothing
End Sub

Immanent

Answer 3

Another approach what I use to load an Excel file:

Public Sub Command7_Click()
    Dim FD As FileDialog
    Dim fileNamePath As String, fileExtension As String, fileName As String
    If fileNamePath = "" Then
        Set FD = Application.FileDialog(msoFileDialogOpen)
        Dim FileChosen As Integer
        FileChosen = FD.show
        FD.Title = "Choose workbook"
        FD.InitialView = msoFileDialogViewList

        FD.Filters.Clear
        FD.Filters.Add "Excel workbooks", "*.xlsx"
        FD.Filters.Add "All files", "*.*"
        FD.FilterIndex = 1
        FD.ButtonName = "Choose this file"
        If FileChosen <> -1 Then 'didn't choose anything (clicked on CANCEL)
            MsgBox "No file opened", vbCritical
        Else
            fileNamePath = FD.SelectedItems(1)
            fileName = Dir(fileNamePath)
            fileExtension = Right$(fileName, Len(fileName) - InStrRev(fileName, "."))
        End If
        Set FD = Nothing
    End If
End Sub

Clingfish