Solved

Find a Folder and Read Path into Variable

Posted on 2014-10-22
14
182 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 (Access MVP)
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
Independent Software Vendors: 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!

 
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 (Access MVP)
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 500 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

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

728 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