Show Open File Dialog

Question

How would I go about showing an open file  or file select  dialog in access 2007 VBA   I have tried using Application GetOpenFileName as I would in Excel  but this function doesn t exist in Access

User · Answer

In Access 2007 you just need to use Application FileDialog   Here is the example from the Access documentation     Requires reference to Microsoft Office 12 0 Object Library    Private Sub cmdFileDialog Click      Dim fDialog As Office FileDialog    Dim varFile As Variant       Clear listbox contents       Me FileList RowSource            Set up the File Dialog       Set fDialog   Application FileDialog msoFileDialogFilePicker      With fDialog          Allow user to make multiple selections in dialog box          AllowMultiSelect   True          Set the title of the dialog box           Title    Please select one or more files           Clear out the current filters  and add our own          Filters Clear        Filters Add  Access Databases      MDB         Filters Add  Access Projects      ADP         Filters Add  All Files                  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            Loop through each file selected and add it to our list box             For Each varFile In  SelectedItems             Me FileList AddItem varFile          Next        Else          MsgBox  You clicked Cancel in the file dialog box         End If    End With End Sub   As the sample says  just make sure you have a reference to the Microsoft Access 12 0 Object Library  under the VBE IDE   Tools   References menu

User · Answer

Addition to what Albert has already said   This code  a mashup of various samples  provides the ability to have a SaveAs dialog box  Function getFileName   As String     Dim fDialog    As Object     Set fDialog   Application FileDialog msoFileDialogSaveAs      Dim varFile As Variant      With fDialog         AllowMultiSelect   False         Title    Select File Location to Export XLSx            InitialFileName    jeffatwood xlsx       If  Show   True Then        For Each varFile In  SelectedItems          getFileName   varFile        Next     End If End With End Function

User · Answer

My comments on Renaud Bompuis s answer messed up   Actually  you can use late binding  and the reference to the 11 0 object library is not required   The following code will work without any references     Dim f    As Object   Set f   Application FileDialog 3    f AllowMultiSelect   True   f Show    MsgBox  file choosen      amp  f SelectedItems Count    Note that the above works well in the runtime also

User · Answer

I have a similar solution to the above and it works for opening  saving  file selecting   I paste it into its own module and use in all the Access DB s I create   As the code states it requires Microsoft Office 14 0 Object Library   Just another option I suppose   Public Function Select File InitPath  ActionType  FileType        Requires reference to Microsoft Office 14 0 Object Library       Dim fDialog As Office FileDialog     Dim varFile As Variant       If ActionType    FilePicker  Then         Set fDialog   Application FileDialog msoFileDialogFilePicker            Set up the File Dialog      End If     If ActionType    SaveAs  Then         Set fDialog   Application FileDialog msoFileDialogSaveAs      End If     If ActionType    Open  Then         Set fDialog   Application FileDialog msoFileDialogOpen      End If     With fDialog          AllowMultiSelect   False           Disallow user to make multiple selections in dialog box          Title    Please specify the file to save open               Set the title of the dialog box          If ActionType  lt  gt   SaveAs  Then              Filters Clear               Clear out the current filters  and add our own               Filters Add FileType        amp  FileType         End If          InitialFileName   InitPath           Show the dialog box  If the  Show method returns True  the           user picked a file  If the  Show method returns           False  the user clicked Cancel          If  Show   True Then          Loop through each file selected and add it to our list box              For Each varFile In  SelectedItems                  return the subroutine value as the file path  amp  name selected                 Select File   varFile             Next         End If     End With End Function

User · Answer

I agree John M has best answer to OP s question  Thought not explictly stated  the apparent purpose is to get a selected file name  whereas other answers return either counts or lists  I would add  however  that the msofiledialogfilepicker might be a better option in this case  ie    Dim f As object Set f   Application FileDialog msoFileDialogFilePicker  dim varfile as variant  f show with f      allowmultiselect   false      for each varfile in  selecteditems         msgbox varfile      next varfile end with   Note  the value of varfile will remain the same since multiselect is false  only one item is ever selected   I used its value outside the loop with equal success  It s probably better practice to do it as John M did  however  Also  the folder picker can be used to get a selected folder  I always prefer late binding  but I think the object is native to the default access library  so it may not be necessary here

[vba] Show "Open File" Dialog

Examples related to vba

Examples related to ms-access