Solved

Find a Folder and Read Path into Variable

Posted on 2014-10-22
14
178 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
  • 5
  • 4
  • 2
  • +2
14 Comments
 
LVL 47

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 119

Expert Comment

by:Rey Obrero
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
 
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 47

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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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 119

Accepted Solution

by:
Rey Obrero 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

920 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now