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

David and John,

Thanks for your assistance with this question.

With the info that you supplied and a little work over the weekend, I now have a nice chunk of code that works perfectly.

I appreciate the help.

Brad



-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of David Emerson
Sent: Friday, October 17, 2014 5:24 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [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

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