Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Find a Folder and Read Path into Variable

Posted on 2014-10-22
14
Medium Priority
?
189 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 49

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 49

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

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.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
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…

571 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