Find a Folder and Read Path into Variable

I got a great code block from some Expert a couple of years ago. It allows you to open a Windows navigation pane from within Access and find a file wherever it might be. When you click the "Open" button it pulls the file name and path back into a text box on the calling form.

I want to create a verion of this - or get a new alternative - that pulls the Folder Path ONLY back into the text box. The user could double click in the folders as navigation is taking place. Right now, the Open button drills down into the folder if you click it and have not selected a specific file. But in this case, all I want is the folder itself - not a file.

Any suggestions appreciated.
Buck_BeasomDatabase DesignerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
from my ipad, so not as thorough as normal.

search EE on file dialog, you should get several great examples.

it will allow you to do files or folders, with very little coding.
Rey Obrero (Capricorn1)Commented:
assuming the complete filepath is in the string variable  strFIleName, you can get the folder path with

Me.txtFolder = Left(strFileName, InStrRev(strFileName, "\"))
Here's a link to the Microsoft support page on the FileDialog object.

In your case you would change this line in their sample:
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
to this:
Set fDialog = Application.FileDialog(msoFileDialogFolderPicker)

Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Helen FeddemaCommented:
Try this:

Public Function GetFolderPath() As String
'Created by Helen Feddema 23-Oct-2014
'Modified by Helen Feddema 23-Oct-2014
'Requires a reference to the Office object model

On Error GoTo ErrorHandler
   Dim fd As Office.FileDialog
   'Create a FileDialog object as a Folder Picker dialog box.
   Set fd = Application.FileDialog(msoFileDialogFolderPicker)
   With fd
      .Title = "Browse for folder where __________"
      .ButtonName = "Select"
      .InitialView = msoFileDialogViewDetails
      '.InitialFileName = strPath
      If .Show = -1 Then
         GetFolderPath = CStr(fd.SelectedItems.Item(1))
         Debug.Print "User pressed Cancel"
      End If
   End With

   Exit Function

   MsgBox "Error No: " & Err.Number _
      & " in GetFolderPath procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Function

Open in new window

Buck_BeasomDatabase DesignerAuthor Commented:
The Dim fd As Office.FileDialog throws a Compile Error:

User-defined type not defined.

Do I need to change settings in Access or define something in the Global Module?

You need to add a reference to Microsoft Office 11.0 Object Library or similar version.
Dale FyeOwner, Developing Solutions LLCCommented:
or you can use:

Dim fd as Object     'Office.FileDialog

if you want late binding.  I generally start with early binding, then change the declaration statements as above and remove the reference to the Office XX.X Object Library.
Buck_BeasomDatabase DesignerAuthor Commented:
I do not find any reference to Microsoft File Dialog, File Dialog or anything similar to add in references.

When I try to execute the function coded above and substitute Dim fd As Object in for Dim fd As Office.FileDialog as suggested, I get the error "Error no" -2147467259 In GetFolderPath procedure; Description: Method 'FileDialog' of object "_Application' failed."

I think the short way out of this is for me to figure out how to get the Office.FileDialog into references so when I try to declare it, the app doesn't error out. Any suggestions appreciated.

Rey Obrero (Capricorn1)Commented:
you need the Microsoft Office xx.0 Object library

xx = 12 for A2007; 14 for A2010
Buck_BeasomDatabase DesignerAuthor Commented:
I have the Microsoft Office 14.0 database engine Object Librady already selected in the references box.

What do I do next? The attempt to Dim a variable as an "Office .FileDialog" still throws an error.

Rey Obrero (Capricorn1)Commented:
use code like this

Dim fd As Object
Set fd = Application.FileDialog(4)
With fd
     .Title = "Select Folder"
     .InitialFileName = CurrentProject.path & "\"
     If .Show Then
          Me.txtFolderPath = .selecteditems(1)
     End If
End With

this assuming you have a textbox named txtFolderPath in you form
Buck_BeasomDatabase DesignerAuthor Commented:
OK, Rey! This works great. Only one question: The navigation box that pops up has the "Views" drop down disabled, so you can't switch to "Detail" view. Not a fatal problem, but given the size of some of the folders we have to navigate, the "Detail" view would be nice.

I'm heading out on vacation for a week, so I'll give you a couple hours to respond and if I don't hear back I will just accept your solution and award the points as it does accomplish what I wanted.

Great tip!!!!
Rey Obrero (Capricorn1)Commented:

Dim fd As Object, txtFolderPath
Set fd = Application.FileDialog(4)
With fd
      .InitialView = 2   'add this line and try the values 1 or 2
     .Title = "Select Folder"
     .InitialFileName = CurrentProject.Path '& "\"
     If .Show Then
          txtFolderPath = .SelectedItems(1)
     End If
End With

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Buck_BeasomDatabase DesignerAuthor Commented:
You da Man! This is not the first time you have helped me out, and I am sure it will not be the last!

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.