BrowseFolder

[kirkm]

Is there a dialog, or way, that allows user to browse certain folders, rather than everything, (which I have now)?
There's 2 folders only I'd like to have available.
Thanks for any help

 

[arnelgp]

Dont give them read access to the folder.

 

[kirkm]

Thanks... but I'm not sure how to do that or if it would be a solution. I sort of need a selective "BrowseFolder" routine that would either show just the wanted folders, or start at nth level down.
If such a thing doesn't exist I could try some other way (listbox?)

 

[Orthodox Dave]

Here's some bedtime reading:
http://analystcave.com/vba-application-filedialog-select-file/

I've used it successfully, but not in a restrictive way, although the text explains how to set up filters.

The downside is that filedialog requires Microsoft Office 11.0 Object Library, which is not available in Access 2016. The solution is to set up API Late Binding. I have done this successfully by copying code, even though I don't really understand how it works! Someday I will read up on this dark art!

 

[kirkm]

Thank you Dave, that was good reading, learnt a few handy things!

BUT (why is there always a But ?) it doesn't work as hoped. Possibly the rider Does not support file filders (sic) is why, although can it ?
It opens the drive root (I think) and ignores any attempt to start further down the tree.

fDialog.InitialFileName= "\\Tools\Misc"

This shows everything under Tools, but my hope is to show everything under Misc.
Can it do this with some extra magic tweak, do you know?
(This will never be used with Access 2016 so no worries about the Object library)

 

[kirkm]

Later I found a d-click opens that folder and shows the contents. You can select another folder there. Brilliant!
Under that there's a blank area called "Folder Name". What / how is that populated?

 

[Orthodox Dave]

OK it's a while since I did this and my code is set up with API Late Binding, but the parts that might help you are:

Dim fDialog As Object, BaseSearchPath As String 'the path to start the search
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
BaseSearchPath = "X:\XXX\XXX" 'the starting point for the search
Dim FullPath As String 'This is the full path to the file we will have found in our search

'Optional: FileDialog properties
fDialog.AllowMultiSelect = False
fDialog.TITLE = "Select a file"
fDialog.InitialFileName = BaseSearchPath
'Optional: Add filters
fDialog.Filters.Clear
fDialog.Filters.Add "All files", "*.*"
 
'Show the dialog. -1 means success!
If fDialog.Show = -1 Then
FullPath = fDialog.SelectedItems(1)

  If MsgBox("Do you want to open " & FullPath & "?", vbYesNo) = vbYes Then
  Application.FollowHyperlink FullPath, , True
  End If 'MsgBox("Do you want to open " ...

I'm not sure exactly what you meant by the d-click and "Folder Name" but hope this answers the question anyway, without too much back and forth!

If you have more problems, it might help if you include your code.

 

[kirkm]

Hi Dave, thanks for that.. have it working and it is helpful but is it all ? (No last End If).
It looks for a file although I want just folders. It was good to see how it would launch a file.
What I have is just what you gave me in an earlier message:

Function GetAFolder(pName) As String

    Dim fDialog As FileDialog
    Set fDialog = Application.FileDialog(msoFileDialogFolderPicker)
    
    fDialog.Title = "Select a folder"
    fDialog.InitialFileName = pName
 
    If fDialog.Show = -1 Then
        GetAFolder = fDialog.SelectedItems(1)
    End If
    
End Function

I pass in the parent name and it shows all the Folders present. Double click one of them and it moves to *that* folder and so on. As you select a Folder if it could put that name into the "Folder Name" drop down it would be more logical to then click OK. It's not a major but just curious if its possible.

 

[Orthodox Dave]

Sorry yes the "End If" is about a mile further down the sub! Mine is designed for the user to browse to a folder then click a button to copy and save the file they require to a location and in a filename format that the database can use to open it via hyperlinks.

You could simplify further like:

Dim fDialog As Object, BaseSearchPath As String 'the path to start the search
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
BaseSearchPath = "X:\XXX\XXX" 'the starting point for the search

'Optional: FileDialog properties
fDialog.AllowMultiSelect = False
fDialog.TITLE = "SOME NAME OTHER THAN Select a file"
fDialog.InitialFileName = BaseSearchPath
fDialog.Show
Exit Sub

 

[Gasman]

What is the result if you add an extra

"\"

at the end?

Thank you Dave, that was good reading, learnt a few handy things!

BUT (why is there always a But ?) it doesn't work as hoped. Possibly the rider Does not support file filders (sic) is why, although can it ?
It opens the drive root (I think) and ignores any attempt to start further down the tree.

fDialog.InitialFileName= "\\Tools\Misc"

This shows everything under Tools, but my hope is to show everything under Misc.
Can it do this with some extra magic tweak, do you know?
(This will never be used with Access 2016 so no worries about the Object library)

 

[Pat Hartman]

The downside is that filedialog requires Microsoft Office 11.0 Object Library, which is not available in Access 2016.

You would use the MS Office xx.0 library with xx being whatever is loaded.

To pick folders use msoFileDialogFolderPicker rather than msoFileDialogFilePicker