Selecting folders

[RichardMGreen]

Hi all

I've got the following code:-

Function GetFolderName(Optional OpenAt As String) As String
    Dim lCount As Long
    With Application.FileDialog(msoFileDialogFolderPicker)
        .InitialFileName = OpenAt
        .Show
            For lCount = 1 To .SelectedItems.Count
                GetFolderName = .SelectedItems(lCount)
            Next lCount
    End With
End Function

which allows users to select a folder which is used further on in the code. If you select Cancel when the folder picker appears, I get a null string returned and it causes problems further on.

How would I modify it so that if a user selects Cancel it keeps the current folder?

I've tried checking if the selected.items.count is more than 0 (placed around the for/next statement) but that's not helped.

Any help greatly appreciated.

 

[dk]

Hi

Something like this maybe?

Function GetFolderName(Optional OpenAt As String) As String
    Dim lCount As Long
    Dim sCurrentFolder As String
    Dim sSelectedFolder As String
    
    sCurrentFolder = CurDir
    
    With Application.FileDialog(msoFileDialogFolderPicker)
        .InitialFileName = OpenAt
        .Show
            For lCount = 1 To .SelectedItems.Count
                sSelectedFolder = .SelectedItems(lCount)
            Next lCount
    End With
    
    If Len(sSelectedFolder) = 0 Then
        GetFolderName = sCurrentFolder
    Else
        GetFolderName = sSelectedFolder
    End If
    
    
End Function

The function will return either the folder chosen or the current folder if the user cancels (i.e. the folder that would be displayed if you chose File, Open from the application.

HTH
DK

 

[RichardMGreen]

Thanks for that, but it's not quite what I wanted.
I was trying to get it read the contents of a field in a table where the current one is stored.

I tried reading it into a variable using docmd.runsql but it highlights the runsql section and gives me an "expected function or procedure" error.

 

[Norie]

Richard

That's different question, if you want to grab a single value for the folder name you could try DLookUp.

Then you can use DK's code for selecting a file and dealing with the user pressing Cancel.

 

[RichardMGreen]

Brilliant, thanks folks. Got it working as I wanted now.