How to get the selected path and file name opened using the file dialog box?

Question

I need the path name and the name of the file that opens in the file dialog. I want to show this information with a hyperlink on my sheet.

With this code, I have a file path:

Sub GetFilePath() Set myFile = Application.FileDialog(msoFileDialogOpen) With myFile .Title = "Choose File" .AllowMultiSelect = False If .Show <> -1 Then Exit Sub End If FileSelected = .SelectedItems(1) End With ActiveSheet.Range("A1") = FileSelected End Sub 

I'm still looking for a way to get the file name.

Answer 1

try it

 Sub Demo() Dim lngCount As Long Dim cl As Range Set cl = ActiveCell ' Open the file dialog With Application.FileDialog(msoFileDialogOpen) .AllowMultiSelect = True .Show ' Display paths of each file selected For lngCount = 1 To .SelectedItems.Count ' Add Hyperlinks cl.Worksheet.Hyperlinks.Add _ Anchor:=cl, Address:=.SelectedItems(lngCount), _ TextToDisplay:=.SelectedItems(lngCount) ' Add file name 'cl.Offset(0, 1) = _ ' Mid(.SelectedItems(lngCount), InStrRev(.SelectedItems(lngCount), "\") + 1) ' Add file as formula cl.Offset(0, 1).FormulaR1C1 = _ "=TRIM(RIGHT(SUBSTITUTE(RC[-1],""\"",REPT("" "",99)),99))" Set cl = cl.Offset(1, 0) Next lngCount End With End Sub 

Answer 2

You can get any part of the file path using FileSystemObject. GetFileName (file path) gives you what you want.

Modified code below:

 Sub GetFilePath() Dim objFSO as New FileSystemObject Set myFile = Application.FileDialog(msoFileDialogOpen) With myFile .Title = "Choose File" .AllowMultiSelect = False If .Show <> -1 Then Exit Sub End If FileSelected = .SelectedItems(1) End With ActiveSheet.Range("A1") = FileSelected 'The file path ActiveSheet.Range("A2") = objFSO.GetFileName(FileSelected) 'The file name End Sub 

Answer 3

To extract only the file name from the path, you can do the following:

 varFileName = Mid(fDialog.SelectedItems(1), InStrRev(fDialog.SelectedItems(1), "\") + 1, Len(fDialog.SelectedItems(1))) 

Answer 4

I think you want this:

 Dim filename As String filename = Application.GetOpenFilename Dim cell As Range cell = Application.Range("A1") cell.Value = filename 

Answer 5

I think this is the easiest way to get to what you want.

JMK loan is responsible for the first part, and part of the hyperlink was adapted from http://msdn.microsoft.com/en-us/library/office/ff822490(v=office.15).aspx

 'Gets the entire path to the file including the filename using the open file dialog Dim filename As String filename = Application.GetOpenFilename 'Adds a hyperlink to cell b5 in the currently active sheet With ActiveSheet .Hyperlinks.Add Anchor:=.Range("b5"), _ Address:=filename, _ ScreenTip:="The screenTIP", _ TextToDisplay:=filename End With 

Answer 6

The code starts searching for files from the root colon, if I want to start searching from a specific directory, so that every time I go to this directory, where I have to put it. I made it like

 Sub GetFilePath() FileSelected = "G:\Audits\A2010" Set myFile = Application.FileDialog(msoFileDialogOpen) With myFile .Title = "Choose File" .AllowMultiSelect = False If .Show <> -1 Then Exit Sub End If FileSelected = .SelectedItems(1) End With ActiveSheet.Range("C14") = FileSelected End Sub 

But he could not access "G: \ Audits \ A2010"

Answer 7

I think this will do:

 Dim filename As String filename = Application.GetOpenFilename 

Answer 8

From Office 2010, we will not be able to use the common dialog box control, so it's nice to use the Application object to get the desired results.

Here I have a text box and a “Command” button - paste the following code under the command button event, which will open the file dialog box and add the file name to the text box.

 Dim sFileName As String sFileName = Application.GetOpenFilename("MS Excel (*.xlsx), *.xls") TextBox1.Text = sFileName 

Answer 9

The following command is enough to get the file path from the dialog box -

 my_FileName = Application.GetOpenFilename("Excel Files (*.tsv), *.txt") 

Answer 10

FileNameOnly = Dir (.SelectedItems (1))

Answer 11

After searching for various websites that are looking for a solution on how to separate the full path from the file name, as soon as complete, complete information is obtained from the “Open File” dialog box and seeing how “complex” solutions were provided for how new to Excel I wondered if there could be a simpler solution. So I started working on it myself, and I came to this opportunity. (I have no idea if someone got the same idea before. Being so simple if anyone is, I apologize.)

 Dim fPath As String Dim fName As String Dim fdString As String fdString = (the OpenFileDialog.FileName) 'Get just the path by finding the last "\" in the string from the end of it fPath = Left(fdString, InStrRev(fdString, "\")) 'Get just the file name by finding the last "\" in the string from the end of it fName = Mid(fdString, InStrRev(fdString, "\") + 1) 'Just to check the result Msgbox "File path: " & vbLF & fPath & vbLF & vblF & "File name: " & vbLF & fName 

WHAT IS IT!!! Just give it a try and let me know how this happens ...

Answer 12

 Sub GetFilePath() Set myFile = Application.FileDialog(msoFileDialogOpen) With myFile .Title = "Choose File" .AllowMultiSelect = False If .Show <> -1 Then Exit Sub End If FileSelected = Replace(.SelectedItems(1), .InitialFileName, "") End With ActiveSheet.Range("A1") = FileSelected End Sub