Create Access table from Excel spreadsheet (1 Viewer)

[reglarh]

I am sure this has been covered before but I cannot find exactly what I need, which is simply to create and populate an Access table from an Excel sheet i.e. using the field names from row 1 of the Excel sheet and the data from subsequent rows. I need this to run from within the Access environment.

 

[monheimx9]

This is what I use in my database, I also call the Dialbox to select the excel file I want to import

Public Sub ImportToTable()
    Dim sTBL As String: sTBL = "temp_tbl_import"
    Dim sFname As String, sNameCheck As String
    sFname = dialBoxFiles()
    sNameCheck = Right$(sFname, 5)
    sNameCheck = Left$(sNameCheck, 4)
    If sNameCheck = ".xls" Then
        DoCmd.DeleteObject acTable, sTBL
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, sTBL, sFname, True
    Else
        MsgBox "The selected file isn't" & vbCrLf _
             & """xlsx, xlsm, xlsb""", vbOKOnly Or vbExclamation, "Please select a compatible file"
    End If
End Sub

Public Function dialBoxFiles() As String
    Dim fDial As Office.FileDialog
    Set fDial = Application.FileDialog(msoFileDialogFilePicker)
    With fDial
        .AllowMultiSelect = False
        .Title = "Sélectionner un ficher hey ho"
        .Filters.Clear
        .Filters.Add "Excel", "*.xlsx; *.xlsb; *.xlsm"
        .InitialView = msoFileDialogViewList
        If .Show Then
            dialBoxFiles = .SelectedItems(1)
        End If
    End With
End Function

EDIT: I stripped down my code from functions that would have been useless in that usecase, I didn't tested it so be aware

 

[The_Doc_Man]

I am sure this has been covered before but I cannot find exactly what I need, which is simply to create and populate an Access table from an Excel sheet i.e. using the field names from row 1 of the Excel sheet and the data from subsequent rows. I need this to run from within the Access environment.

Just to be clear, the significant part of what monheimx9 showed you was the TransferSpreadSheet call. The code sample is NOT necessarily wrong. I am just highlighting the functional part and giving you a reference if you want to simplify or tune the process.

DoCmd.TransferSpreadsheet method (Access)

Office VBA reference topic

learn.microsoft.com

 

[Ranman256]

import the spreadsheet
it will create the table.