Data Import & Export

How to Import an XLSX Spreadsheet into Microsoft Access Using VBA

Muhammad Talha Oct 10, 2026 870 views

Question details

The user needs to automate the import of modern Excel (.xlsx) files into a Microsoft Access database using VBA.

How to Import an XLSX Spreadsheet into Microsoft Access Using VBA

Product: Microsoft Access
Device & OS: not provided
Scenario: Importing an XLSX workbook into Access using VBA via a form control, while avoiding legacy database compatibility errors and file path issues.
Observed behavior: Legacy Access databases often fail to import modern XLSX files if the constructed file path, filename, or file extension in the VBA code is incorrect or missing.

Before you start

Ensure that your Windows File Explorer is configured to show file extensions so you can verify the exact extension (like .xlsx or .xlsb) and avoid duplicate extensions in your file path.

Solution 1Recommended

Use DoCmd.TransferSpreadsheet with Validated File Paths

Validate the file name and path using the Immediate window, then execute the native TransferSpreadsheet command configured for modern Excel formats.

The most common cause of import failure is an improperly constructed file path from the form control. Modern Excel formats require the specific acSpreadsheetTypeExcel12 constant.

Validate the form control input

Read the value from your form control (e.g., selectwo.Text). Ensure that the variable used for the filename includes the correct .xlsx extension exactly once.

Verify the file path with Debug.Print

Before running the import command, insert 'Debug.Print yourFilePathVariable' in your VBA code. Open the Immediate Window (Ctrl+G) to verify that Access is attempting to open the correct, complete file path.

Execute the import command

Use the command 'DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "YourTableName", yourFilePathVariable, True' to import the spreadsheet into the specified Access table.

Use DoCmd.TransferSpreadsheet with Validated File Paths

No Additional References Required: Because DoCmd.TransferSpreadsheet is a native Access method, you normally do not need to add the Microsoft Excel Object Library reference in your VBA environment.

Free Microsoft Office alternative

Looking for a Lightweight Office Suite Alternative?

If you frequently manage spreadsheets but want to avoid the heavy resource usage and high costs of traditional database software, WPS Office provides a free, highly compatible, and lightning-fast alternative to Microsoft Office.

1. Download the Installer: Visit the official WPS Office website and download the free installation package.
2. Install WPS Office: Run the setup file and follow the simple on-screen instructions to install the lightweight suite.
3. Open Your Spreadsheets: Launch WPS Spreadsheet to open, edit, and manage your XLSX files seamlessly.

100% format compatibility with Microsoft Excel (.xlsx, .xls, .csv).Free, lightweight, and installs in seconds.Familiar user interface ensures a seamless migration with zero learning curve.

Frequently Asked Questions

Which Access constant should I use for importing XLSX files?

You should use 'acSpreadsheetTypeExcel12'. This specific constant is designed to correctly process modern Excel workbook formats (Excel 2007 and newer), including .xlsx and .xlsb extensions.

Why does my VBA script fail with a 'file cannot be found' error?

This error typically occurs if the constructed file path string is incorrect. It often happens when Windows hides file extensions, causing users to omit the '.xlsx' extension or inadvertently type it twice (e.g., filename.xlsx.xlsx). Always use Debug.Print to check the final path.

Do I need to enable Microsoft Excel Object Library references to import spreadsheets?

No. The 'DoCmd.TransferSpreadsheet' command is built natively into Microsoft Access. You do not need to load external Excel object libraries in your VBA editor to perform standard spreadsheet imports or exports.

Excel & Data Problems