?
Solved

Find a Folder and Read Path into Variable

Posted on 2014-10-22
14
Medium Priority
?
184 Views
Last Modified: 2014-11-21
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.
0
Comment
Question by:Buck_Beasom
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
  • +2
14 Comments
 
LVL 48

Expert Comment

by:Dale Fye
ID: 40396842
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.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40396853
assuming the complete filepath is in the string variable  strFIleName, you can get the folder path with

Me.txtFolder = Left(strFileName, InStrRev(strFileName, "\"))
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 40398604
Here's a link to the Microsoft support page on the FileDialog object.
http://msdn.microsoft.com/en-us/library/office/ff196794(v=office.15).aspx

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

Ron
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 31

Expert Comment

by:Helen Feddema
ID: 40400040
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))
      Else
         Debug.Print "User pressed Cancel"
      End If
   End With

ErrorHandlerExit:
   Exit Function

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

End Function

Open in new window

0
 

Author Comment

by:Buck_Beasom
ID: 40432891
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?

Thanks.
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 40432904
You need to add a reference to Microsoft Office 11.0 Object Library or similar version.
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 40433202
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.
0
 

Author Comment

by:Buck_Beasom
ID: 40440234
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.

Thanks.
0
 
LVL 120

Expert Comment

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

xx = 12 for A2007; 14 for A2010
0
 

Author Comment

by:Buck_Beasom
ID: 40443403
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.

Thanks.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40443508
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
0
 

Author Comment

by:Buck_Beasom
ID: 40458340
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!!!!
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 40458392
try

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
0
 

Author Closing Comment

by:Buck_Beasom
ID: 40458828
You da Man! This is not the first time you have helped me out, and I am sure it will not be the last!

Thanks!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question