Expert guide

Import Excel into Microsoft Access with VBA

TransferSpreadsheet into a staging table, validate, then append: the pattern that survives messy files

Free Cost Audit

Call us at +1 (385) 386-3860

Short answer: clear a staging table whose fields are all Short Text, import with DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "tblStaging", path, True, add the source file name, run validation queries that flag bad rows, then append only valid, non-duplicate rows to the real table with type conversions. Log each import. For one-time moves of whole workbooks into a proper database, see converting Excel to an Access database.

Excel files arrive from everywhere: supplier price lists, bank exports, timesheets, CRM extracts, other departments' trackers. Importing them straight into live tables works until the file has a blank row, a text value in a number column or a changed header. The staging pattern below turns imports into a dependable routine.

TransferSpreadsheet in One Line

DoCmd.TransferSpreadsheet TransferType:=acImport, _
    SpreadsheetType:=acSpreadsheetTypeExcel12Xml, _
    TableName:="tblStaging", _
    FileName:="C:\Imports\Prices.xlsx", _
    HasFieldNames:=True, _
    Range:="Prices!A1:F5000"
ArgumentNotes
acSpreadsheetTypeExcel12Xml.xlsx files; use acSpreadsheetTypeExcel12 for .xlsb/.xlsm and older types for .xls
TableNameExisting table: rows are appended; missing table: Access creates it and guesses types
HasFieldNamesTrue if row 1 holds headers; headers must match field names when appending to an existing table
Range"SheetName!" for a whole sheet, "SheetName!A1:F5000" for a block, or a named range

Why Import Into Staging

  • Type guessing

    If Access creates the table, it guesses each column's type from the first rows. A column of numbers with one 'n/a' further down produces import errors.

  • Messy rows

    Totals rows, blank lines, notes at the bottom and repeated headers all import as data.

  • Duplicates

    The same file imported twice doubles your data.

  • Changed layouts

    A renamed column fails the append; staging lets you detect and report it.

Create tblStaging once with all fields as Short Text (Long Text for long notes), named exactly as the Excel headers, plus SourceFile, RowStatus and RowError fields.

The Full Import Routine

Public Function ImportPriceFile(ByVal filePath As String) As Long
    Dim db As DAO.Database, fileName As String
    On Error GoTo Fail
    Set db = CurrentDb
    fileName = Mid$(filePath, InStrRev(filePath, "\") + 1)

    If DCount("*", "tblImportLog", "FileName='" & Replace(fileName, "'", "''") & "'") > 0 Then
        MsgBox fileName & " has already been imported.", vbExclamation: Exit Function
    End If

    db.Execute "DELETE FROM tblStaging", dbFailOnError
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "tblStaging", _
                              filePath, True, "Prices!"

    ' Tag rows and remove obvious junk
    db.Execute "UPDATE tblStaging SET SourceFile='" & Replace(fileName, "'", "''") & _
               "', RowStatus='New'", dbFailOnError
    db.Execute "DELETE FROM tblStaging WHERE Nz(SKU,'')='' AND Nz(Price,'')=''", dbFailOnError

    ' Validate
    db.Execute "UPDATE tblStaging SET RowStatus='Error', RowError='Price not numeric' " & _
               "WHERE RowStatus='New' AND Not IsNumeric(Price)", dbFailOnError
    db.Execute "UPDATE tblStaging SET RowStatus='Error', RowError='Unknown SKU' " & _
               "WHERE RowStatus='New' AND SKU Not In (SELECT SKU FROM tblProducts)", dbFailOnError

    ' Append valid rows with type conversion
    db.Execute "INSERT INTO tblPrices (SKU, Price, EffectiveDate, SourceFile) " & _
               "SELECT SKU, CCur(Price), CDate(EffectiveDate), SourceFile FROM tblStaging " & _
               "WHERE RowStatus='New'", dbFailOnError
    ImportPriceFile = db.RecordsAffected

    db.Execute "INSERT INTO tblImportLog (FileName, ImportedOn, RowsImported, RowsRejected) " & _
               "VALUES ('" & Replace(fileName, "'", "''") & "', Now(), " & ImportPriceFile & ", " & _
               DCount("*", "tblStaging", "RowStatus='Error'") & ")", dbFailOnError
    Exit Function
Fail:
    MsgBox "Import failed: " & Err.Description, vbCritical
End Function

Rejected rows stay in tblStaging with a reason until the next import, so a user can open a simple form, see what failed and fix the source file.

Importing Every File in a Folder

Public Sub ImportFolder(ByVal folder As String)
    Dim f As String
    If Right$(folder, 1) <> "\" Then folder = folder & "\"
    f = Dir(folder & "*.xlsx")
    Do While Len(f) > 0
        If Left$(f, 2) <> "~$" Then                ' skip Excel lock files
            ImportPriceFile folder & f
            Name folder & f As folder & "Processed\" & f
        End If
        f = Dir()
    Loop
End Sub

Moving files to a Processed subfolder means the folder only ever contains new work. Create the subfolder once, or check for it in code.

Import vs Link vs Power Query

ApproachBest whenWatch out for
Import (this guide)Data must be validated, related and keptNeeds a repeatable routine
Link to the workbookRead-only reference to a live workbookSlow; read-only; breaks if the file moves or layout changes
Clean in Excel with Power Query firstFiles need heavy reshapingTwo-step process
Import via an API instead of ExcelSource system offers an APIIntegration effort, but no files at all

If Excel exports come from a system with an API, skipping the spreadsheet altogether is often worth it; see parsing JSON in Access VBA. If you are moving a whole team off a workbook into Access, read converting Excel to Access and Excel to database solutions.

FAQ

Importing Excel into Access FAQ

TransferSpreadsheet, types and duplicates

Use DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "tblStaging", filePath, True, "Sheet1!A1:H5000". The True means the first row contains field names; the range argument is optional. Import into a staging table first, validate, then append clean rows to your real tables.

Access decides each column's data type from the first rows of the sheet. If a column starts with numbers and later contains text (or the reverse), later values may fail to import and end up in an ImportErrors table. Importing into an existing staging table with Short Text fields avoids the guessing.

Link when the workbook is a live source that people keep updating and you only need to read it occasionally. Import when you need the data inside Access for validation, relationships and performance. Linked Excel tables are read-only in modern Access.

Loop through the folder with Dir("C:\Imports\*.xlsx"), import each file into the staging table with TransferSpreadsheet, record the source file name on each row, then move the file into a Processed folder so it is not imported twice.

Keep a log of imported file names and reject repeats, and use a natural key (invoice number, order number) with a unique index in the destination table. Append from staging with a query that excludes keys already present.