Using FileDialog in Excel to return path and filename separately

[grantwilliams]

Hi all,

I'm trying to use a single FileDialog to return the path (directory) and filename separately. Does anybody know how to do this? I have managed it using two separate FileDialogs and have attached the code for this below. Hopefully you can see from this why I need to be able to separate the path from the filename.

Thanks,

Grant

Sub Browse1()

    Dim PathLength, FileLength, GetLength, GetFile, GetPath, fd As FileDialog, fp As FileDialog
    
    Set fp = Application.FileDialog(msoFileDialogFolderPicker)
    Dim vrtSelectedPath
    With fp
        .InitialFileName = ""
        If .Show = -1 Then
            For Each vrtSelectedPath In .SelectedItems
                PathLength = Len(vrtSelectedPath)
                GetPath = vrtSelectedPath
            Next vrtSelectedPath
        End If
    End With
    Set fp = Nothing
   
    
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    Dim vrtSelectedFile
    With fd
        .InitialFileName = ""
        If .Show = -1 Then
            For Each vrtSelectedFile In .SelectedItems
                FileLength = Len(vrtSelectedFile)
                GetLength = FileLength - PathLength - 1
                GetFile = Right(vrtSelectedFile, GetLength)
                Range("A20") = "='" & GetPath & "\[" & GetFile & "]Data'!A3"
            Next vrtSelectedFile
        End If
    End With
    Set fd = Nothing
    

    Set OpenSheet = Nothing
    
End Sub

 

[grantwilliams]

Derrr!

Figured it out - used the InStrRev function to find the last '\'. Here is the code for anyone who's interested:

Sub Browse1()

    Dim PathLength, FileLength, GetLength, GetFile, GetPath, fd As FileDialog
    
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    Dim vrtSelectedFile
    With fd
        .InitialFileName = ""
        If .Show = -1 Then
            For Each vrtSelectedFile In .SelectedItems
                FileLength = Len(vrtSelectedFile)
                PathLength = InStrRev(vrtSelectedFile, "\")
                GetLength = FileLength - PathLength
                GetPath = Left(vrtSelectedFile, GetLength)
                GetFile = Right(vrtSelectedFile, GetLength)
                Range("A20") = "='" & GetPath & "\[" & GetFile & "]Data'!A3"
            Next vrtSelectedFile
        End If
    End With
    Set fd = Nothing
        
End Sub