MS Access VBA: Open Excel Files and Read Their Data

VBA & macros · Updated November 20, 2026

Yes. In Microsoft Access VBA on Windows, you can open an Excel workbook by creating an Excel application object and calling its Workbooks.Open method. If you only need to bring spreadsheet data into Access, DoCmd.TransferSpreadsheet is often simpler and avoids automating the Excel interface.

What “open an Excel file” can mean

There are two different tasks people commonly describe as opening an Excel file. One is launching Excel and controlling the workbook: VBA can open a workbook, select a worksheet, read or change cells, save changes, and close Excel. The other is importing or linking spreadsheet data into an Access table. That task can usually be done directly from Access without displaying Excel.

Choose based on what the next step is. If an Access form needs a value from a particular cell, or a process must update a workbook and save it, Excel automation is appropriate. If you want to load a worksheet as rows and columns in Access, use the database import feature instead. Opening a workbook in Excel does not, by itself, put its data into an Access table.

Excel automation controls the desktop Excel application; it is not a way to open a workbook in Excel for the web. Access is a Windows desktop application, and the VBA automation examples here are for Access on Windows. Excel for Mac and Excel for the web do not provide a Mac or browser version of Access VBA automation.

Requirements and workbook paths

To use CreateObject("Excel.Application") and work with Excel objects, the computer needs a compatible desktop version of Microsoft Excel installed. Access and Excel are commonly installed together, but Access can also be installed without Excel. In that case, Excel automation cannot start the Excel application. Direct spreadsheet import may still be available depending on the installed Access components and file format.

Use a full path to the workbook, such as C:\Data\Quarterly Report.xlsx, rather than relying on the current folder. A file picker or a value stored in a settings table can supply the path. Check that the file exists before opening it, and consider what happens if a user selects a file with a different name or extension.

  • For an ordinary local file, confirm that the Access user can read the folder and file.
  • For a network location, prefer a stable UNC path such as \\Server\Shared\Reports\Report.xlsx in unattended or shared workflows; mapped drive letters can differ between users or sessions.
  • If the workbook is password-protected, Excel may request a password. Handle that deliberately rather than assuming the file will open unattended.

Late binding and early binding

The example below uses late binding. Variables are declared as Object, and CreateObject starts Excel at run time. This avoids requiring a specific Excel object-library reference in the Access project, which can make a database easier to move between computers with different Office versions. The trade-off is that the VBA editor offers less Excel-specific autocomplete and compile-time checking, and Excel constants must be supplied as numbers or declared by the programmer.

With early binding, in the VBA editor choose Tools > References and select the installed Microsoft Excel Object Library. Then declare variables using Excel types such as Excel.Application and Excel.Workbook. Early binding gives better IntelliSense and named constants, but the reference must resolve on each computer. For a database distributed to several Office installations, late binding is often the less brittle choice.

A safe VBA example that opens and reads a workbook

This Access procedure opens a workbook read-only, reads cell A1 from the first worksheet, prints the value in the Immediate window, and closes both the workbook and Excel. In the VBA editor, the Immediate window is available with Ctrl+G on Windows. Save a copy of the database and workbook before running or adapting a macro: VBA actions cannot be undone with Ctrl+Z, and later changes to the code could write to files.

Public Sub ReadExcelCell()
    Dim xlApp As Object
    Dim xlBook As Object
    Dim xlSheet As Object
    Dim filePath As String
    Dim firstValue As Variant

    On Error GoTo ErrHandler

    filePath = "C:\Data\Quarterly Report.xlsx"
    If Len(Dir$(filePath)) = 0 Then
        Err.Raise vbObjectError + 1000, "ReadExcelCell", "Workbook not found: " & filePath
    End If

    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = False
    Set xlBook = xlApp.Workbooks.Open(filePath, 0, True)
    Set xlSheet = xlBook.Worksheets(1)

    firstValue = xlSheet.Range("A1").Value2
    Debug.Print firstValue

CleanExit:
    On Error Resume Next
    If Not xlBook Is Nothing Then xlBook.Close False
    If Not xlApp Is Nothing Then xlApp.Quit
    Set xlSheet = Nothing
    Set xlBook = Nothing
    Set xlApp = Nothing
    Exit Sub

ErrHandler:
    MsgBox "Could not read the workbook: " & Err.Description, vbExclamation
    Resume CleanExit
End Sub

The second and third arguments passed to Workbooks.Open are UpdateLinks and ReadOnly. Passing 0 avoids updating external workbook links automatically, and True requests a read-only opening. The example uses Value2 to retrieve the cell’s underlying value without Excel’s Currency or Date conversion behavior. Change the path and worksheet selection to match the file; using Worksheets("Summary") is clearer than relying on the first sheet if the workbook’s tab order can change.

Reading data, writing changes, and closing Excel

Once the workbook and worksheet objects exist, VBA can access ranges in the same way Excel automation generally does. For example, xlSheet.Range("B2").Value2 reads one cell, while assigning a value to that property changes the workbook in memory. To keep a change, save the workbook before closing it. Do not save if the procedure is intended only to inspect the file.

When transferring a rectangular block of data, read or write the whole range at once rather than making a separate Excel call for every cell. A range’s Value2 can be assigned to a two-dimensional VBA array, which is typically more efficient for larger datasets. Check for blank cells, error values, and unexpected headers before using the returned values in Access; an Excel cell can contain more than plain text or a number.

Always close the workbook and quit the Excel application that the procedure created, including when an error occurs. Leaving hidden Excel processes running is a frequent consequence of incomplete cleanup. If the user already has Excel open, creating a separate application instance and quitting that instance is generally safer than attaching to and closing an application the user may be using.

Importing or linking with TransferSpreadsheet

If the goal is to make worksheet rows available as Access records, use Access’s spreadsheet transfer feature rather than opening Excel cell by cell. The VBA method is DoCmd.TransferSpreadsheet. Its arguments specify whether to import, export, or link; the spreadsheet format; the Access table or query; the file path; whether the first row contains field names; and, optionally, a worksheet or range.

For example, an import can create or populate an Access table from an Excel worksheet, while a link makes the worksheet data available through a linked table without copying all the data into a new Access table. With current Excel workbooks, the Access spreadsheet type commonly used for .xlsx files is acSpreadsheetTypeExcel12Xml; older .xls workbooks use a different spreadsheet type. Match the selected type to the actual file, and verify the field names and data types after import. Mixed values in an Excel column can cause Access to interpret data differently than expected.

Use automation when you need Excel-specific actions such as recalculating a workbook, changing formatting, or saving a customized template. Use transfer when the job is fundamentally moving tabular data between Excel and Access. For occasional manual work, Access’s External Data import commands may be easier to configure than VBA.

Common problems and safety considerations

  • File not found: Confirm the full path, extension, spelling, permissions, and whether a network share is available in the account running Access.
  • Excel does not start: Check that desktop Excel is installed and repair the Office installation if necessary. A workbook file association alone is not a substitute for the Excel automation application.
  • Workbook opens with a prompt: Passwords, damaged-file warnings, external links, Protected View, and read-only recommendations can interrupt automation. Test the workflow with the real kinds of files it will receive, and do not blindly suppress alerts that may protect users or data.
  • Wrong worksheet or blank result: Confirm the tab name and cell address. A formula cell may return an empty string, and a cell that looks blank can contain spaces or a formula.
  • Excel remains in Task Manager: Ensure that every success and error path closes the workbook and calls Quit on the Excel instance created by the procedure.

Opening files from email or other untrusted sources deserves caution. Excel workbooks can contain macros, external links, and content that prompts for user action. Do not enable or execute workbook macros simply because Access opened the file. Keep Access VBA itself within your organization’s trusted-location or code-signing policy, and avoid using broad security changes as a shortcut to make automation run.

Frequently Asked Questions

Can Access VBA open an Excel file without showing Excel?
Yes. Set the Excel application object’s Visible property to False. Excel still runs in the background, so the procedure must close the workbook and quit the application when finished.

Does Microsoft Excel have to be installed?
It must be installed for the Excel.Application automation method. Access can also import or link spreadsheet data using its transfer features, but availability depends on the Office/Access components and file format on that computer.

How do I open a specific worksheet?
After opening the workbook, refer to it by tab name, for example xlBook.Worksheets("Summary"), or by index with xlBook.Worksheets(1). A tab name is usually more reliable when sheet order may change.

Can Access VBA import Excel data into a table?
Yes. Use DoCmd.TransferSpreadsheet with an import transfer type, the matching spreadsheet format, a destination table name, and the workbook path. Inspect the imported fields and records because Excel’s mixed or loosely typed columns may not map as expected.

Can I use this Excel automation code on a Mac?
No, not as written. Microsoft Access is a Windows desktop application, and this code uses Windows Office COM automation. Excel for Mac does not provide the same Access VBA automation environment.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.