Selecting a File then returning only the Path it is in.

Randal

Board Regular
Joined
Mar 30, 2009
Messages
142
I am using the following code. Problem is that it when you select.. the filenames are not there. I am trying to select the filename, but only return its path to a cell. Can everyone help?

Please advise and thanks. -R-

Sub Test_GetFolder()
MsgBox GetFolder("Get My Folder", ThisWorkbook.Path)

Range("A3") = ThisWorkbook.Path

End Sub

Function GetFolder(Optional sTitle As String = "Select Folder", _
Optional sInitialFilename As String)
Dim myFolder As String
With Application.FileDialog(msoFileDialogFolderPicker)
If sInitialFilename = "" Then sInitialFilename = ThisWorkbook.Path
.InitialFileName = sInitialFilename
.Title = "Greetings"
If .Show = -1 Then
GetFolder = .SelectedItems(1)
If Right(GetFolder, 1) <> "\" Then
GetFolder = GetFolder & "\"
End If
Else: GetFolder = ""
End If
End With
End Function
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
I should have mentioned something else. The code I posted in the thread does return the path, but is missing the last \ that needs to be at the end of the path. -R-
 
Upvote 0
Can you not just add on the final "\" to the returned path?

Code:
Sub Test_GetFolder()
Dim strPath As String
 
strPath = GetFolder("Get My Folder", ThisWorkbook.Path)

Range("A3") = strPath & "\"

End Sub
 
Upvote 0
First, you are using a folder picker, hence you won't see any files. Second you are not doing anything with the result of the function. Try something like this:
Code:
Sub Test_GetFolder()
dim strPath as string
strpath =  GetPath(ThisWorkbook.Path)
Range("A3") = strPath
End Sub
 
Function GetPath(strDefault As String) As String
   Dim dlg As FileDialog, varReturn
   Dim strTemp As String
   Set dlg = Application.FileDialog(msoFileDialogFilePicker)
   With dlg
      .InitialFileName = strDefault
      If .Show = -1 Then
         strTemp = .SelectedItems(1)
         GetPath = Left$(strTemp, InStrRev(strTemp, "\"))
      End If
   End With
End Function
 
Upvote 0
Thank you!

You understood the need exactly and it worked great. As you can see by my number of posts I am new to this Forum. I appreciate the quick responses and help with this.. I already flagged MrExcel as a fav. -R-
 
Upvote 0
Further to your PM, if you want the filename as well, but in a separate cell, it is easier to just return the full name from the function and then split it:
Code:
Sub Test_GetFolder()
dim strPath as string, lngPathSep as long
strpath =  GetPath(ThisWorkbook.Path)
if strpath <> "" then
   lngPathSep = InStrRev(strpath, "\"))
   Range("A3") = Left$(strpath, lngPathSep)
   Range("A4") = mid$(strpath, lngPathSep + 1)
End if
End Sub
 
Function GetPath(strDefault As String) As String
   Dim dlg As FileDialog, varReturn
   Dim strTemp As String
   Set dlg = Application.FileDialog(msoFileDialogFilePicker)
   With dlg
      .InitialFileName = strDefault
      If .Show = -1 Then
         GetPath= .SelectedItems(1)
      End If
   End With
End Function
 
Upvote 0
The fifth line down is red..

lngPathSep = InStrRev(strpath, "\"))

Is this a typo? -R-
 
Upvote 0
figured it out.. there was an extra ")" at the end of the line. -R-

Works great, much thanks... -R-
 
Upvote 0
Yes, sorry - copying and pasting in a rush gets me every time!
 
Upvote 0

Forum statistics

Threads
1,213,522
Messages
6,114,112
Members
448,549
Latest member
brianhfield

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top