Import Excel spreadsheet with user-chosen file

[Erik Pygott]

Is it possible to use the TransferSpreadsheet function without hardcoding in the filename? I'm working on a DB that will have 10-15 people using daily and I'd like to have an import function where they can click a button, a dialog box opens asking them to select the file to import, then have it import the users and append to an existing table. I have it working with the file and path coded into the function call, but I'd like to have the flexibility of letting the users choose the file location if it's not too much work.

[gnawoncents]

Check out the Access Help topic for FileDialog Object. That should get you to where you want.

[NeoPa]

This might help to get you started.

First off, for this approach you need a reference to "MS VBA Extensibility" (of whatever appropriate version).

The following routine does most of the main work for you :

'FSBrowse (File System Browse) allows the operator to browse for a file/folder.
Public Function FSBrowse(Optional strStart As String = "", _
                         Optional lngType As MsoFileDialogType = _
                             msoFileDialogFolderPicker) As String
    FSBrowse = ""
    With Application.FileDialog(dialogType:=lngType)
        .Title = "Browse for "
        Select Case lngType
        Case msoFileDialogOpen
            .Title = .Title & "File to open"
        Case msoFileDialogSaveAs
            .Title = .Title & "File to SaveAs"
        Case msoFileDialogFilePicker
            .Title = .Title & "File"
        Case msoFileDialogFolderPicker
            .Title = .Title & "Folder"
        End Select
        .InitialFileName = strStart
        .AllowMultiSelect = False
        .InitialView = msoFileDialogViewDetails
        If .Show Then FSBrowse = .SelectedItems(1)
    End With
End Function

The following routine behind a Command Button (cmdFolder in this case) is an example of how you can call this. I store the value in lblFolder.Caption hence its use as a parameter to the call :

Private Sub cmdFolder_Click()
    Dim strPath As String
 
    strPath = FSBrowse(Me.lblFolder.Caption)
    If strPath > "" Then _
        Me.lblFolder.Caption = Replace(strPath & "\", "\\", "\")
End Sub