Document Editing Problems

Automate Access to Word Mail Merge from Excel Worksheet via VBA

Maira Mehtab Sep 25, 2026 869 views

Question details

The user wants to automate a mail merge from Access to Word using an exported Excel file, but needs a way to stop Word from prompting for the worksheet name.

Automate an Access to Word Mail Merge from an Excel Worksheet

Product: Microsoft Word / Excel (VBA)
Device & OS: not provided
Scenario: Automating a Word mail merge process via VBA, using an Excel (.xlsx) file exported from Microsoft Access as the data source.
Observed behavior: When the VBA script runs, Word interrupts the automated process by asking the user whether to use the worksheet name with or without a dollar sign.

Before you start

Before modifying your VBA script, ensure that the exported Excel file is closed and that you have the exact spelling of the target worksheet name.

Solution 1Recommended

Use the Connection Argument in the OpenDataSource Method

Specify the exact worksheet name in your VBA script to automatically bypass the Word prompt during the mail merge process.

When using the `MailMerge.OpenDataSource` method in VBA, Microsoft Word will prompt the user to select a data table if the specific worksheet isn't declared. Providing the `Connection` argument tells Word exactly where to pull the data from.

Open the VBA Editor

In Microsoft Access or Word, press ALT + F11 to open the Visual Basic for Applications (VBA) editor and locate your mail merge macro.

Locate the OpenDataSource Method

Find the line of code that begins with `MailMerge.OpenDataSource` or `.OpenDataSource` if you are using a `With` statement.

Add the Connection Argument

Append the `Connection` parameter to the method, setting it equal to your worksheet name. For example: `Connection:="Updatedetailsone"`.

Test the Macro

Save your code and run the macro. Word should now seamlessly connect to the specified worksheet in the Excel file without pausing to ask for confirmation.

Use the Connection Argument in the OpenDataSource Method

Formatting the Worksheet Name: If the standard worksheet name does not work, try appending a dollar sign to the end of the sheet name in the connection string (e.g., `Connection:="Updatedetailsone$"`), as OLE DB often requires this syntax for Excel tables.

Free Microsoft Office alternative

Try WPS Office for Seamless Document Management

If you frequently encounter complex VBA integration issues in Microsoft Office, consider trying WPS Office. It provides a lightweight, user-friendly, and highly compatible alternative with intuitive built-in Mail Merge features.

1. Download WPS Office: Download and install the free WPS Office suite on your device.
2. Open WPS Writer: Launch WPS Writer and navigate to the 'References' tab on the top ribbon.
3. Use Mail Merge: Click 'Mail Merge' and select 'Open Data Source' to easily import your Excel worksheet and generate your documents visually.

Fully compatible with Microsoft Word (.docx) and Excel (.xlsx) file formats.Built-in, easy-to-use Mail Merge wizard that doesn't require complex VBA coding.Lightweight software that opens quickly and runs smoothly on older devices.Free to use with a familiar, easy-to-navigate tabbed interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Word ask for the worksheet name during a VBA mail merge?

Word prompts for the worksheet name because an Excel workbook can contain multiple sheets. If the target sheet isn't explicitly defined in the `OpenDataSource` connection string, Word requires manual confirmation to ensure it pulls data from the correct table.

Do I need a dollar sign ($) after the worksheet name in the connection string?

In many OLE DB or ODBC data connections, Excel worksheet names require a trailing dollar sign (e.g., "Sheet1$"). If your VBA macro fails or still prompts you without one, append the $ to your connection string.

Can I automate this without writing VBA code?

While fully automating the process from an Access export to a Word mail merge typically requires VBA, you can manually accomplish the task using the Mail Merge wizard in Word or WPS Writer to link an exported Excel file with just a few clicks.

Document & File Issues