How to Access an Already Open Excel Workbook from a Word VBA Macro
Question details
The user needs a Word VBA macro to interact with an Excel workbook that is already open, but the macro fails to detect the workbook.
- Product
- Microsoft Word and Excel
- Device & OS
- not provided
- Scenario
- Running a VBA macro in Microsoft Word to pull or push data to an Excel workbook that the user already has open on their desktop.
- Observed behavior
- The macro reports zero open Excel workbooks despite one being open, typically because the code references a new, hidden Excel application instance or the global Excel.Workbooks collection.
Ensure you have saved your progress in both Word and Excel, as testing and debugging VBA macros can occasionally cause applications to become unresponsive.
Use GetObject and Explicitly Reference the Application Workbooks
Capture the existing Excel application instance using GetObject rather than creating a new background instance.
Using the global 'Excel.Workbooks' command often forces VBA to look at a newly created background process instead of the Excel window you are looking at. To fix this, you must bind your object variable to the active Excel session using 'GetObject' and then explicitly ask for the workbooks belonging to that specific session.
In Microsoft Word, press Alt + F11 to open the Visual Basic for Applications (VBA) editor.
In your macro code, attempt to hook into the running Excel application by adding: Set oExApp = GetObject(, "Excel.Application"). Ensure you use error handling (On Error Resume Next) in case Excel isn't open yet.
Directly beneath the GetObject call, check if the application was found. If not, create it: If oExApp Is Nothing Then Set oExApp = CreateObject("Excel.Application").
Instead of using 'Excel.Workbooks', change your workbook variable assignment to reference your captured application object: Set oExWbks = oExApp.Workbooks.
Terminate Hidden Excel Background Processes
Clear out invisible Excel processes that might be capturing the macro's connection attempt.
Try WPS Office for a Seamless and Lightweight Experience
If you are encountering frustrating VBA cross-application errors in Microsoft Office, consider trying WPS Office. It provides a lightweight, unified interface for document, spreadsheet, and presentation management, reducing the friction of cross-app workflows.
Frequently Asked Questions
Why does GetObject fail even when Excel is clearly open?
GetObject can fail if Excel is running with different administrator privileges than Word (e.g., Word is running as administrator but Excel is not), or if Excel is open but has not fully registered itself in the Windows Running Object Table because a dialog box is actively open.
How do I target a specific workbook once connected to the Excel instance?
Once you have successfully assigned 'oExApp.Workbooks' to a variable, you can reference a specific open workbook by its name using syntax like: Set myWorkbook = oExApp.Workbooks("DataSheet.xlsx").
Why does my macro open a new, empty Excel window instead of finding my open file?
This happens when your macro code uses 'CreateObject("Excel.Application")' without first attempting to use 'GetObject'. CreateObject will always spin up a brand-new, empty application instance, ignoring any workbooks you already have open.
