How to Fix Excel VBA Run-Time Error 91 When Copying Data to Word
Question details
The user needs to resolve run-time error 91 in Excel VBA, which occurs when attempting to copy a formatted range into a Word document using the Selection.Goto statement.
- Product
- Microsoft Excel, Microsoft Word
- Device & OS
- not provided
- Scenario
- Automating cross-application tasks from Excel to Word using VBA macros.
- Observed behavior
- A run-time error 91 ('Object variable or With block variable not set') stops the macro execution because an object variable, specifically related to the Word document's Selection object, lacks a valid reference.
Ensure your macro code has error handling temporarily disabled (e.g., commenting out 'On Error Resume Next') so you can accurately identify the exact line triggering Error 91.
Initialize Objects Correctly and Use ActiveWindow Selection
Correctly set up your Word application and document objects, and adjust your selection reference to target the document's active window.
Run-time error 91 occurs when an object variable evaluates to 'Nothing' or hasn't been properly assigned. When controlling Word from Excel, referencing the global 'Selection' object directly often fails because Excel does not know which application's Selection to use.
Use the 'Set' keyword to assign your Word application object variable. For example: Set wrdApp = CreateObject("Word.Application").
Assign the document to an object variable while opening it. Use syntax like: Set wrdDoc = wrdApp.Documents.Open("C:\Path\To\Your\File.docx"). Ensure the file path is absolutely correct.
Instead of using wrdApp.Selection.Goto or simply Selection.Goto, explicitly reference the document's active window. Change your code to read wrdDoc.ActiveWindow.Selection.Goto.
Verify File Paths and Object States
Ensure the Word document successfully opens by confirming the file path, thus preventing object variables from remaining empty.
Execute Your VBA Macros Flawlessly with WPS Spreadsheet
WPS Office provides robust support for VBA macros, allowing you to seamlessly automate tasks between spreadsheets and documents. Experience high compatibility with Microsoft Office scripts and formats for free.
- 1. Download and Install WPS Office: Get the latest version of WPS Office and ensure you select the version with VBA support enabled.
- 2. Open Your Macro-Enabled File: Launch WPS Spreadsheet and open your .xlsm file containing the cross-application macro.
- 3. Enable Macros and Run: Click 'Enable Macros' in the security warning prompt. Access the Developer tab, open the VBA Editor, and execute your code to copy the formatted range to WPS Writer.
Frequently Asked Questions
What exactly does 'Object variable or With block variable not set' mean in VBA?
This error (Error 91) means your VBA code is trying to use an object (like a Worksheet, Document, or Range) that hasn't been assigned an actual reference in memory using the 'Set' keyword.
Why does my code work with early binding but fail with late binding?
With early binding, you reference the Word library directly, giving you access to application-specific constants (like wdGoToBookmark). In late binding, these constants aren't recognized unless explicitly defined in your code, which can cause methods like Selection.Goto to fail.
Can Trust Center settings cause Run-time Error 91?
Generally, no. While Trust Center settings can block macros or add-ins entirely, Error 91 is specifically a coding issue related to uninitialized objects, not a security or trusted publisher block.
