[AccessD] How to List Files in a folder and then Select a Specific File using Access 2007 VBA

Brad,

Here is a function to get you started.

Regards

David Emerson
Dalyn Software Ltd
Wellington, New Zealand



Public Function basGetFolderOrFileName(strTitle As String, strCurDir As
String, strCurFile As String, Flags As Integer, Filter As Integer)
'For directories leave CurFile as empty string. For files leave CurDir as
empty string.
'basGetFolderOrFileName("Select Folder", Nz(Me!txtFileFolderPAMaint, ""),
"", 1, 1)
'basGetFolderOrFileName("Select File", "", Nz(Me!txtFilePAMaint, ""), 2, 5)

    basGetFolderOrFileName = ""
            
    Dim fDialog As FileDialog
    If Flags = 1 Then
        Set fDialog = Application.FileDialog(msoFileDialogFolderPicker)
        fDialog.InitialFileName = strCurDir
    Else
        Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
        fDialog.InitialFileName = strCurFile
        fDialog.Filters.Clear
        Select Case Filter
            Case 2: fDialog.Filters.Add "Zip Files", "*.zip"
            Case 3: fDialog.Filters.Add "Text Files", "*.txt"
            Case 4: fDialog.Filters.Add "Database Files", "*.mdb"
            Case 5: fDialog.Filters.Add "Excel Spreadsheets", "*.xlsx"
            Case 6: fDialog.Filters.Add "CSV Files", "*.csv"
            Case Else: fDialog.Filters.Add "All Files", "*.*"
        End Select
    End If
    
    'Allow user to make multiple selections in dialog box
    'fDialog.AllowMultiSelect = True
             
    'Set the title of the dialog box.
    fDialog.Title = strTitle
 
    ' 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 fDialog.Show = True Then
        basGetFolderOrFileName = Trim(fDialog.SelectedItems(1))
    End If
        
End Function


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Brad Marks
Sent: Saturday, 18 October 2014 10:53 a.m.
To: Access Developers discussion and problem solving
Subject: [AccessD] How to List Files in a folder and then Select a Specific
File using Access 2007 VBA

All,

I am working on an Access 2007 application which will obtain data from one
of multiple Excel files.

I would like to be able to list all of the Excel files in one folder and
then obtain the name of the file that is selected by the user.

I have been experimenting with "Application.FileDialog" but keep running
into problems.  I have very little experience in this realm. 

Does anyone have an example of how to do this or possibly a link to a good
example on the web?

Thanks,

Brad

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com