Import from excel to access

[Tabasco]

Is it possible to preset all the steps in import from excel buttom in access in vba? Or well, not all the steps the user should only need to find the excel file on the computor?

ive tried the follow syntax

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "Sheet1", [U]"C:\test.xls"[/U], True

however, the excelfile alwas change name so i want the user to be able to search (and fill out) that part

[ADezii]

In my opinion, the simplest approach would be to:

1. Open the standard Office Dialog filtered for Excel Files only and allow the User to select only a single Excel File (*.xls). You can also allow other Excel Extensions if needed.
2. Import the Selected Excel File into a Table named Sheet1. This can also be easily modified for varying Table Names.
   

   'Must 1st set a Reference to the Microsoft Office XX.X Object Library
   Dim dlgOpen As FileDialog
   
   Set dlgOpen = Application.FileDialog(msoFileDialogOpen)
   
   With dlgOpen
     .AllowMultiSelect = False         'Only a Single File
     .ButtonName = "Import"
     .InitialView = msoFileDialogViewLargeIcons
     .InitialFileName = CurrentProject.Path
       .Filters.Clear
       .Filters.Add "Excel Files", "*.xls"
          If .Show = -1 Then
            DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "Sheet1", _
                                      .SelectedItems(1), True
          End If
   End With
   
   'Set the object variable to Nothing.
   Set dlgOpen = Nothing

[NeoPa]

Any parameter can be passed as a variable rather than a literal reference. This means anything that is passed as a parameter (and this includes the names of the Access object to export as well as the name of the file) can be determined in advance using whatever code you want to use, then passed to the procedure. Essentially then, the answer to your question is "Yes, as far as the parameters are concerned".

[Tabasco]

It worked beautifully! Thank you very much! This is much more simple than to use Acces buttom "Import from excel"