[AccessD] "Modern" replacement for CommonDlg

Arthur,

This is a different way of doing the same thing using the Office Library.
You have to include which ever version of the Office dll that you are 
using in the references.

You will need to comment out the line that contains "udf_WriteErrorToLog"

See if the following helps you out:

2 of 2

Public Function udf_SelectFolderDialogBox() As String
     '****************************************************************************
     '* Purpose:  :
     '*      (1) Shows the user a dialog box allowing selection of a folder.
     '* Arguments: None
     '* Returns:
     '*      (1) If the user selected the CANCEL button on the dialog box, a
     '*          zero-length string is returned.
     '*      (2) If the user selected a file, the fully-qualified 
path is returned.
     '* Calls subroutines: None
     '* Creates Arrays: None
     '* Uses Arrays: None
     '* Uses Tools->References:
     '*      (1) Microsoft Office 10.0 Object Library  ... or ...
     '*      (2) Microsoft Office 12.0 Object Library ... etc ...
     '****************************************************************************

     'On Error GoTo Err_udf_SelectFolderDialogBox

     Dim fDialog As Office.FileDialog
     Dim varFile As Variant
     Dim strProc As String
     Dim strCodeLocn As String
     Dim strMsg As String
     Dim strRetVal As String
     Dim varDescOfFile As Variant

     '************************************************************************
     strCodeLocn = "Initialize values."
     '************************************************************************
     strProc = "udf_SelectFolderDialogBox"
     strRetVal = ""

     '************************************************************************
     strCodeLocn = "Set up the File Dialog."
     '************************************************************************
     Set fDialog = Application.FileDialog(msoFileDialogFolderPicker)
     '        Options are:
     '        msoFileDialogFilePicker        'OK for Access and Excel
     '        msoFileDialogFolderPicker      'OK for Access and Excel
     '        msoFileDialogOpen              'Not OK for Access
     '        msoFileDialogSaveAs            'Not OK for Access
     '        Note: instead of the option:
     '                   msoFileDialogFilePicker to select a file
     '              you could also use the option:
     '                   msoFileDialogFolderPicker to select a folder

     With fDialog
         .AllowMultiSelect = False           'Disable multiple selections.
         .Title = varDescOfFile              'Set the title of the dialog box.
         .Filters.Clear                      'Clear out the current filters.

         '.Filters.Add "", varExtensions
         '.Filters.Add ".TXT file from SAP", "*.TXT"  'assign file filters
         '.Filters.Add "Access Databases", "*.MDB"
         '.Filters.Add "All Files", "*.*"

         '********************************************************************
         strCodeLocn = "Show the dialog box."
         '********************************************************************
         'If the .Show method returns True, the user picked at least one file.
         'If the .Show method returns False, the user clicked Cancel.
         If .Show = True Then
             strRetVal = .SelectedItems.Item(1)
         End If
     End With

Exit_udf_SelectFolderDialogBox:
     udf_SelectFolderDialogBox = strRetVal
     Exit Function

Err_udf_SelectFolderDialogBox:
     '************************************************************************
     '* Write the error message to the log table and display it to the user.
     '************************************************************************
     strMsg = udf_WriteErrorToLog(strModule, strProc, strCodeLocn, 
Erl, Err.Number, Err.Description)
     MsgBox (strMsg)
     GoTo Exit_udf_SelectFolderDialogBox
End Function


At 10:16 PM 7/9/2011, you wrote:
> >>>> fuller.artful at gmail.com 06-07-2011 14:39>>>
> > Thanks for this. It does appear to work nicely for selecting a 
> file, but I'm
> > not sure how to modify it to return the selected directory rather than a
> > file in it.
> >
> > A.
>

Robert L. Stewart
www.WeBeDb.com
www.DBGUIDesign.com
www.RLStewartPhotography.com