Opening Word from Excel using VBA – Several errors

1
votes

I am currently trying to open Microsoft Word from Microsoft Excel using VBA. I found these resources online:

Based on them, I came up with the following code:

Code 1

Dim wordApp As Word.Application
Set wordApp = CreateObject("Word.Application")

Running the code with Word already open

Excel stops responding.

Running the code with Word closed

Word opens, but Excel gives me the error Out of memory or Automation error and wordApp is not defined.

Code 2

Dim wordApp As Word.Application
Set wordApp = GetObject(,"Word.Application")

Running the code with Word already open

Excel throws the error Out of memory and wordApp is not defined.

Running the code with Word closed

Excel throws the error ActiveX component can't create object or return reference to this object and wordApp is not defined.

System information

OS: macOS Mojave (10.14.6)

Excel and Word Version: 16.36

References in Excel:

Visual Basic For Applications, Microsoft Excel 16.0 Object Library, Microsoft Forms 2.0 Object Library, Microsoft Office 16.0 Object Library, Microsoft Word 16.0 Object Library, OLE Automation

Conclusion

These errors are quite confusing and I could not fix them by reading Microsoft's error pages. For instance, I continuously get the error Out of memory (see above), although I have around 10 GB of free RAM space. How can I get this to work both on my machine (see above) as well as any other machine reliably?

1
excelvbaautomationms-word

1 Answers

1
votes

Combining your both approaches in only one will look like this, and it shouldn't return any error.

If the error still persists, it is not connected to the code itself... Only reference to Microsoft Word 16.0 Object Library necessary.

Sub openWordApp()
  Dim wordApp As Word.Application

  On Error Resume Next
   Set wordApp = GetObject(, "Word.Application")
   If Err.Number <> 0 Then
      Err.Clear: On Error GoTo 0
      Set wordApp = CreateObject("Word.Application")
   End If
   On Error GoTo 0
   wordApp.Visible = True
   Debug.Print wordApp.Documents.count
End Sub

Please, also check if not multiple Word sessions are open... Maybe you checked the code many times with Word session Visible = False and there still are a lot of such sessions open. Just maybe, but it would be good to check it.