Yes. Access VBA can open an Excel workbook by controlling the Excel application, or it can import worksheet data directly into an Access table. Use Excel automation when you need the workbook itself; use an import when your goal is to work with rows and fields in Access.
What “open an Excel file” means in Access
There are two different tasks that people often mean by opening an Excel file. The first is launching Excel and opening a workbook, so a person can view it or VBA can work with its worksheets, cells, charts, and other Excel objects. The second is bringing the spreadsheet’s tabular data into an Access table. These approaches use different tools and produce different results.
Excel automation gives Access a reference to an Excel application object and then to a workbook object. You can make Excel visible, select a worksheet, read or write cell values, and save changes. The workbook remains an Excel workbook; opening it this way does not automatically turn its data into Access records.
For an import, Access reads a worksheet or range and stores the data in a table. Excel does not have to appear on screen, and the imported rows can be queried and joined with other Access tables. This does not preserve the workbook as a working spreadsheet, including its layout, charts, and interactive features. Decide which result you need before choosing the VBA method.
Open a workbook with Excel automation
The following example starts a separate Excel instance, opens a workbook, and leaves Excel visible. Change the sample path to the full path of an existing file. In Access, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, paste the procedure, and run it. On a Mac, the keyboard shortcut for opening the VBA editor differs by Office version; Access itself is a Windows desktop application and has no Mac edition.
Save a copy of your database and any workbook that a macro might change before running VBA. Macro actions cannot be undone with Ctrl+Z.
Option Explicit
Public Sub OpenExcelWorkbook()
Dim filePath As String
Dim xlApp As Object
Dim xlBook As Object
' Use the full path to the workbook.
filePath = "C:\Reports\Sales.xlsx"
If Len(Dir$(filePath)) = 0 Then
MsgBox "File not found: " & filePath, vbExclamation
Exit Sub
End If
On Error GoTo ErrHandler
' Create a separate Excel instance and show it.
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlBook = xlApp.Workbooks.Open(filePath)
Exit Sub
ErrHandler:
MsgBox "Could not open workbook: " & Err.Description, vbExclamation
If Not xlBook Is Nothing Then xlBook.Close False
If Not xlApp Is Nothing Then xlApp.Quit
Set xlBook = Nothing
Set xlApp = Nothing
End Sub
The variables are declared as Object, which is called late binding. It avoids requiring a particular Excel object-library reference in the Access project, and can be useful when a database is shared across Office installations. The trade-off is that the editor cannot provide Excel-specific auto-completion or check object names as thoroughly while you write the code.
After the workbook opens, the object variable can address its contents. For example, the expression xlBook.Worksheets("Summary").Range("B2").Value refers to cell B2 on the Summary worksheet. You could assign that value to an Access variable or use it in a later operation. Check that the named sheet exists before relying on it; an absent worksheet causes a VBA error.
Set the path and decide whether Excel is visible
A full path is the most predictable choice. A path such as C:\Reports\Sales.xlsx is easier to diagnose than a relative path, whose meaning can depend on the current working folder. A network path can also be used if the account running Access has permission to reach it. For a file in a synced cloud folder, use the actual local path available on that computer rather than assuming every user has the same folder name.
The sample checks for the file with Dir$ before starting Excel. That catches a common typo or missing-file problem early, but it does not test whether the workbook is valid, whether another user has locked it, or whether the current user has permission to open it. Excel may still display a password, repair, link-update, or other prompt.
Setting Visible to True is helpful when a person should inspect or continue working in the workbook. For unattended processing, code can set Visible to False instead, but it must also close the workbook and quit the Excel instance when finished. Otherwise, an invisible Excel process may remain running. The sample intentionally leaves the workbook open after a successful call so the user can work with it.
Import worksheet data with TransferSpreadsheet
If you want Access records rather than an open Excel window, use Access’s TransferSpreadsheet method. This example imports an .xlsx workbook into a table named ImportSales. The True argument tells Access to treat the first row as field names. With no range specified, Access uses the worksheet data it identifies in the workbook; if the file contains several sheets or a defined range, specify the appropriate range in the optional argument.
Public Sub ImportSalesSheet()
Dim filePath As String
' Use a workbook with column headings in its first row.
filePath = "C:\Reports\Sales.xlsx"
DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel12Xml, "ImportSales", filePath, True
End Sub
The constant acSpreadsheetTypeExcel12Xml is for modern Excel XML workbooks such as .xlsx. Older .xls files use a different spreadsheet type, such as acSpreadsheetTypeExcel8. Confirm the file extension and format instead of simply renaming a file; changing the extension does not convert its contents.
Importing is useful when Access will validate, query, report on, or combine the data. Plan the table structure first if dependable field types matter. A worksheet column containing dates, numbers, and text may not import in the way you expect, and values that look consistent on screen can still contain blanks or mixed types. Review the resulting table and test with a representative copy of the source file before automating a recurring import.
Choose automation or import
Choose automation when the requirement depends on Excel as an application: displaying the workbook, changing specific cells, recalculating a workbook, or working with Excel features. Choose TransferSpreadsheet when the requirement is to bring tabular data into Access for database operations. Excel automation depends on Excel being installed and can be affected by prompts or application state; a direct import avoids displaying Excel but does not provide control over Excel’s interface.
- Open and show the workbook: use Excel automation and set the application’s Visible property to True.
- Read or edit workbook cells: use automation, and explicitly decide whether to save workbook changes.
- Store rows for Access queries: import with TransferSpreadsheet, then check the table’s fields and records.
- Keep a link to changing source data: consider linking rather than importing, if the workbook’s location and structure are stable.
Importing and linking are not the same. An import creates or fills Access table data at the time the operation runs; later edits to the spreadsheet do not automatically refresh those imported records. A linked table points Access to an external source, so users can view current source data through Access, but the file must remain available and changes to the workbook’s layout can break the link.
Common errors, reliability, and security
File not found usually means the path is wrong, a drive is not connected, or the file is not available to the account running Access. Check the exact path in File Explorer and consider whether a scheduled task runs under a different Windows account. ActiveX component can’t create object can indicate that Excel is unavailable or its Office installation needs attention. Subscript out of range often means a worksheet name in the code does not match the workbook.
Opening a workbook can also trigger Excel prompts, for example when a workbook is password-protected or contains links to other files. Do not suppress every alert just to make automation appear unattended: a prompt may be warning about a real choice, and suppressing it can lead to unintended behavior. Handle expected cases deliberately and test with copies of the actual workbook format.
Opening a workbook through VBA does not make its macros safe, nor should it be treated as a way to bypass Office security. Files from untrusted sources can contain harmful macros or other risky content. Follow your organization’s Office security settings, do not enable content merely to get past a warning, and avoid changing macro-security settings in code to force a workbook to run. Access may also block its own VBA until the database is trusted or its content is enabled according to the organization’s policy.
For repeated runs, add error handling around the complete operation and decide what should happen if the file is already open, locked, or partly processed. If the macro changes a workbook, make the save decision explicit; if it only reads data, close without saving when finished. Keep the source file unchanged until the import or automation has been checked, and log enough information to identify which file and run produced a problem.
Alternatives and platform limits
For a one-time task, opening the file from Excel or importing it through Access’s ribbon may be simpler than writing VBA. For recurring work, a saved Access import specification can help keep import settings consistent. If the data source is a database or a regularly refreshed external service, a database connection or a controlled data pipeline may be more appropriate than treating an Excel workbook as the permanent data store.
Access desktop is available for Windows, not macOS. Excel for Mac can open workbooks, but it cannot host or run Access VBA as a Mac version of Access. Also, Excel for the web does not run desktop VBA macros. If users need to run this Access procedure, plan for a supported Windows installation of Access and the required Office components, and verify the setup on the computers that will use the database.
Frequently Asked Questions
Can Access VBA open Excel without showing the window?
Yes. In the automation example, set xlApp.Visible = False instead of True. Close the workbook and quit the Excel instance in the normal completion path as well as in error handling, so background Excel processes are not left behind.
Does opening an Excel file import its data into Access?
No. Excel automation opens a workbook object; it does not create Access records by itself. Use DoCmd.TransferSpreadsheet to import worksheet data, or use a linked table when you need Access to refer to an external source.
Do I need Excel installed to use TransferSpreadsheet?
TransferSpreadsheet reads spreadsheet data through the Office database components available to Access, rather than controlling the Excel interface. Excel itself is not generally required just to perform this import, but the needed provider and compatible Office installation must be available. Test the operation on the target computer.
Can I open a workbook that is already open in Excel?
The sample uses CreateObject, which starts a separate Excel instance. That means it does not automatically take control of the workbook already displayed in another instance. VBA can attach to a running Excel instance with GetObject, but code must account for the case where Excel is not running and must avoid closing an application it does not own.
Will the macro run on a Mac or in Excel for the web?
Not as an Access VBA procedure. Access is a Windows desktop application, and Excel for the web does not run desktop VBA. A Windows computer with Access is needed to run this Access code; Excel automation also requires a suitable desktop Excel installation.
Related
- Excel Merge Vertical Cells: Steps, Limits, and Alternatives
- Excel Merge Two Cells Into One Cell Without Losing Text
- Excel Merge Multiple Cells Into One Cell: A Clear Guide
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Code to Consolidate Excel Files into One WorkbookTo consolidate matching Excel files into one worksheet, place the macro below in the workbook you want to…
- Excel VBA: Find a Row with Specific Text in a ColumnTo find a row containing specific text in Excel VBA, use the range’s Find method, then read the…
- Excel VBA MATCH Error Handling: Avoid Run-Time ErrorsFor safer Excel VBA MATCH error handling, use Application.Match and test its result with IsError before using it.…
- Read a Text File with Excel VBA: Methods and ExamplesYes. In Excel for Microsoft 365 on Windows, VBA can read a plain-text file with the Open and…
- Excel VBA: Check Whether a String Is Empty or BlankIn Excel VBA, use Len(text) = 0 to check whether a string has no characters. If you also…
- Excel Macro Refresh All: How to Update Workbook DataIn Excel for Microsoft 365 on Windows, use the VBA method ThisWorkbook.RefreshAll to request a refresh of the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.