[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:

1 of 2

Public Function udf_SelectFileDialogBox(Optional varDescOfFile As 
String = "All Files", Optional varExtensions As String = "*.*") As String
     '****************************************************************************
     '* Purpose:  :
     '*      (1) Shows the user a dialog box allowing selection of a file.
     '* Arguments:
     '*      (1) varDescOfFile: The description of the file type to 
be selected.
     '*          For example: SAP Extract File in TXT format
     '*      (2) varExtensions: The file filter to be used when the user is
     '*          provided a view of the files.  For example: *.TXT
     '* 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 and file
     '*          name 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_SelectFileDialogBox

     Dim fDialog As FileDialog
     Dim varFile As Variant
     Dim strProc As String
     Dim strCodeLocn As String
     Dim strMsg As String
     Dim strRetVal As String

     '************************************************************************
     strCodeLocn = "Initialize values."
     '************************************************************************
     strProc = "udf_SelectFileDialogBox(); "
     strRetVal = ""

     '************************************************************************
     strCodeLocn = "Set up the File Dialog."
     '************************************************************************
     Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
     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
     udf_SelectFileDialogBox = strRetVal

Exit_udf_SelectFileDialogBox:
     Exit Function

Err_udf_SelectFileDialogBox:
     '************************************************************************
     '* 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_SelectFileDialogBox
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