Browsing Excel File to import.

[pikoy]

Browse for Excel file to import into Access table

I want a user to click a button, have the file open dialog open, they select a spreadsheet, and then it imports into a table. The problem is the filename can be different every time. The table name will remain constant.

Here is the OnClick:

Private Sub Command8_Click()
On Error GoTo Err_ImportSpreadsheet_Click
 
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel2Xml, "Table1", "T_Staff.xls", "Yes"
 
Exit_ImportSpreadsheet_Click:
    Exit Sub
 
Err_ImportSpreadsheet_Click:
 
    Resume Exit_ImportSpreadsheet_Click
End Sub

 

[CJ_London]

try this function

Function ReturnName(Optional RName As String = "") As String 'find a file to action on
Dim obj_n As Variant
 
    ReturnName = ""
    With Application.FileDialog(3) 'msoFileDialogFilePicker
        .AllowMultiSelect = False
        .InitialFileName = RName
        .Title = "Choose new source location"
        .ButtonName = "Select file"
        If RName <> "" Then
            .Filters.Clear
            .Filters.Add "", "*" & Mid(RName, InStrRev(RName, "."))
        End If
        If .Show = 0 Then
            MsgBox "No file selected"
            Exit Function
        End If
     
        For Each obj_n In .SelectedItems
            ReturnName = obj_n
        Next obj_n
   End With
    
End Function

then you code becomes

 Private Sub Command8_Click()
[COLOR=red]dim FName as string[/COLOR]
 On Error GoTo Err_ImportSpreadsheet_Click
     
     [COLOR=red]FName=returnpath()[/COLOR]
 [COLOR=red]    if FName<>"" then[/COLOR] DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel2Xml, "Table1", FName, "Yes"