CODEWITHSUNDEEP
vbaexcel
Gaus Shaikh
Gaus Shaikh

Reputation: 209

retrieve folder path from file path

I am able to select the file from filedialog function and storing the file path in string. but I also want the folder name of the selected path. Can you please advise as to how to get the folder path from select file.

File selected is : 

U:\public\2016\Macro\CD-CW\109 file.xlsx

I want to show till : 

U:\public\2016\Macro\CD-CW\

My Code

Dim fd As Office.FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
    .AllowMultiSelect = False
    .Title = "Please select the file."
    .Filters.Clear
    .Filters.Add "Excel 2010", "*.xlsx"
    .Filters.Add "All Files", "*.*"
    If .Show = True Then
        selfile = .SelectedItems(1) 'replace txtFileName with your textbox
    End If
End With

Upvotes: 4

Views: 6698

Answers (3)

Sharunas Bielskis
Sharunas Bielskis

Reputation: 1213

One of the solutions is to create a simple function for extracting a folder path from a file that is inside this folder path. My question and proposal is here Related Question. Function code is below:

Function getFolderPathFromFilePath(filePath As String) As String

    Dim lastPathSeparatorPosition As Long

    lastPathSeparatorPosition = InStrRev(filePath, Application.PathSeparator)

    getFolderPathFromFilePath = Left(filePath, lastPathSeparatorPosition - 1)

End Function

In some solutions for this purpose, I used FSO, but it takes resources, and I think it isn't worthy to create FSO object if you need it only for this simple function.

Upvotes: 0

Shai Rado
Shai Rado

Reputation: 33692

You can use Left with InStrRev functions to remove the last string after the first \ found from the right side.

Dim FilePath As String

Dim fd As Office.FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
    .AllowMultiSelect = False
    .Title = "Please select the file."
    .Filters.Clear
    .Filters.Add "Excel 2010", "*.xlsx"
    .Filters.Add "All Files", "*.*"
    If .Show = True Then
        FilePath = Left(.SelectedItems(1), InStrRev(.SelectedItems(1), "\"))
        Debug.Print FilePath 
        selfile = .SelectedItems(1) 'replace txtFileName with your textbox
    End If
End With

Upvotes: 2

rory.ap
rory.ap

Reputation: 35308

This is very simple:

Dim filePath, directoryPath As String
filePath = "U:\public\2016\Macro\CD-CW\109 file.xlsx"
directoryPath = Left(filePath, InStrRev(filePath, "\"))

Upvotes: 9

Related Questions