MS Access Form button that allows user to browse/choose file excel , then imports file to a table

Question

In my form access I want to make a button to browse / choose an excel file and import it in format a table in access.

This is my code.

' Requires reference to Microsoft Office 15.0 Object Library. '

Public Function ImportDocument() As TaskImportEnum
On Error GoTo ErrProc

Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)

With fd
    .InitialFileName = "Some folder"
    .Title = "Dialog Title"
    With .Filters
        .Clear
        .Add "xlsx documents", "*.xlsx", 1
    End With
    .ButtonName = " Import Selected "
    .AllowMultiSelect = False   'Change this to TRUE to enable multi-select

   'If aborted, the Function will return the default value of Aborted
    If .Show = 0 Then GoTo Leave
End With

Dim selectedItem As Variant
For Each selectedItem In fd.SelectedItems
    DoCmd.TransferText acImportDelim, "Raw Data from Import_ Import Specification", "Raw Data from Import", selectedItem, True, ""
Next selectedItem

ImportDocument = TaskImportEnum.Success

Leave:
Set fd = Nothing
On Error GoTo 0
Exit Function

ErrProc:
MsgBox err.Description, vbCritical
ImportDocument = TaskImportEnum.Failure  'Return Failure if error
Resume Leave
End Function

Answer 1

The code in question is part of a solution provided here. However, a few changes are required as the solution provided relates to a CSV file import.

---

In a Standard Module, paste the following:

Public Enum TaskImportEnum
    Aborted = 0 'default
    Success
    Failure
End Enum

Public Function ImportDocument() As TaskImportEnum
    On Error GoTo ErrProc

    Dim fd As FileDialog
    Set fd = Application.FileDialog(msoFileDialogFilePicker)

    With fd
        .InitialFileName = "Some folder"
        .Title = "Dialog Title"
        With .Filters
            .Clear
            .Add "Excel documents", "*.xlsx", 1
        End With
        .ButtonName = " Import Selected "
        .AllowMultiSelect = False   'Change this to TRUE to enable multi-select

       'If aborted, the Function will return the default value of Aborted
        If .Show = 0 Then GoTo Leave
    End With

    Dim selectedItem As Variant
    For Each selectedItem In fd.SelectedItems
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "YourTableName", selectedItem, True, "YourSheetName$" 'Change 'YourTableName' and 'YourSheetName' to the actual names
    Next selectedItem

   'Return Success
   ImportDocument = TaskImportEnum.Success

Leave:
    Set fd = Nothing
    On Error GoTo 0
    Exit Function

ErrProc:
    MsgBox Err.Description, vbCritical
    ImportDocument = TaskImportEnum.Failure  'Return Failure if error
    Resume Leave
End Function

---

On your button's Click event paste the following:

Dim status_ As TaskImportEnum
    status_ = ImportDocument

Select Case status_
    Case TaskImportEnum.Success:
        MsgBox "Success!"

    Case TaskImportEnum.Failure:
        MsgBox "Failure..."

    Case Else:
        MsgBox "Aborted..."
End Select