Excel using Word --- Object is not responding

[Alan]

I am using VBA in Excel to create a Word document containing tables
from a spreadsheet. However, I often get an error message that pops
up: "The object is not responding because the source application may
be busy." When I stop the program at that point, I get the VBA error:
"Automation error - Call was rejected by callee."

Here is how I start Word:

Function StartWord() As Boolean
StartWord = False
' Try to open an existing instance of Word
On Error Resume Next
Set WordApp = GetObject(, "Word.Application")
On Error GoTo 0
' If Word is not started, start a new instance
If WordApp Is Nothing Then
Set WordApp = CreateObject("Word.Application")
End If
WordApp.Visible = False
StartWord = True
End Function

Here are the relevant code snippets that use Word:

' Start the Word application
If Not StartWord() Then
MsgBox "Unable to start Microsoft Word", vbCritical,
"Microsoft Word Error"
Exit Function
End If
' Add a new Word document
WordApp.Application.ScreenUpdating = False
WordApp.Documents.Add
'Set DataTableWS = DataWB.Sheets("table")
' Set up Word document properties
With WordApp.ActiveDocument.PageSetup
.Orientation = wdOrientPortrait
.TopMargin = 20
.BottomMargin = 20
.LeftMargin = 40
.RightMargin = 40
.PageWidth = 700
.PageHeight = 800
.Gutter = 0
End With

It consistently bombs on the statement:

With WordApp.ActiveDocument.PageSetup

Can anyone provide advice on this problem?

Thanks, Alan

 

[Carlos Mallen]

Hi Alan,

You shouldn't be using functions for these tasks. They are meant to be used
when you perform a calculation and want your routine to output something. In
this case, a macro would be better suited for the tasks. Also, you are trying
to create (get) an instance of Word in two separate places in your code. It'd
be simpler if this task were in the same macro.

The On Error Resume Next feature should check only for the error you expect,
not for every error. If something else goes wrong, besides not finding an
open Word application, you won't know it.

Finally, I don't recommend getting to the Document object through the Word
application. You should create an instance of a Document object and work with
it.

Here is the code I 'd use:

Option Explicit
Public Sub CreateWordApplication()
Dim wdApp As Object
Dim doc As Object

On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
If Err.Number = 429 Then 'Catching run-time error '429': ActiveX component
can't create object
Set wdApp = CreateObject("Word.Application")
Err.Clear
End If

wdApp.Visible = True
wdApp.Activate

Set doc = wdApp.Documents.Add
'TODO: Write the instructions for the Word document.
'Consider using a separate macro for this task.

Set wdApp = Nothing
Set doc = Nothing

End Sub

 

[Alan]

Carlos,

I switched functions to subs and made the other changes you
suggested. Now I am getting runtime error 462: "remote server machine
does not exist or is unavailable". I searched for this on the
Internet, but none of the suggestions I found there worked.

Any ideas? Could Word be putting up a prompt I don't know
about?

Alan

P.S. The reason I was using functions returning a boolean was to
facilitate nice error-handling above the level of this code.

 

[JP]

Write a late-bound function that returns the Word.Application object.

Function GetWord() As Object
On Error Resume Next
Set GetWord = GetObject(, "Word.Application")
On Error Goto 0

If GetWord Is Nothing Then
Set GetWord = CreateObject("Word.Application")
End If
End Function

Then start Word like this:

Sub tst()

Dim WordApp As Object
Set WordApp = GetWord

If WordApp Is Nothing Then
MsgBox "Unable to start Microsoft Word", vbCritical, "Microsoft Word
Error"
Exit Function
End If

' use WordApp as you normally would here

End Sub

 

[Alan]

I left out some important information: This does not happen the first
time the code (macro) runs. However, unless I reset things in the
VBE, it happens consistently. If I use the Reset button in the VBE
every time between runs, it works perfectly without errors.

Alan

 

[Carlos Mallen]

I am afraid I have no clue as of what is going on. On my computer I have no
problems when running the macro several times and the error VBA is raising
seems very strange to me. Using the code I wrote, on what line is VBA raising
the error?

Regards,

 

[Alan]

I have tried all the suggestions offered here, to no avail.

I am still getting the runtime error 462: "remote server machine does
not exist or is unavailable" at the code "WordDoc.Tables
(1).PreferredWidth = InchesToPoints(7.82)". If I take that line of
code out, everything works fine.

I have simplified the code, which may be found below. Alan

Public Sub CreateWordDocument(ExcelFilePath As String)
Dim WordApp As Object, WordDoc As Object
Dim LastRow As Long, startRow As Long, endRow As Long
Dim TotRowsPage As Long
' Clear objects
If Not (DataWB Is Nothing) Then Set DataWB = Nothing
If Not (DataTableWS Is Nothing) Then Set DataTableWS = Nothing
ChDir SelectInputFileCES.FolderName(ExcelFilePath)
Application.DisplayAlerts = False
Workbooks.Open ExcelFilePath, UpdateLinks:=xlUpdateLinksNever
Set DataWB = ActiveWorkbook
Set DataTableWS = DataWB.Sheets("table")
Application.DisplayAlerts = True

' Start the Word application
Set WordApp = GetWord()
If WordApp Is Nothing Then
MsgBox "Unable to start Microsoft Word", vbCritical,
"Microsoft Word Error"
Exit Sub
End If
' Add a new Word document
WordApp.Documents.Add
Set WordDoc = WordApp.ActiveDocument
' Set up Word document properties
TotRowsPage = 10
LastRow = DataTableWS.UsedRange.Rows.Count
' Find, copy and format business section
startRow = FindRow("Business", 2, DataTableWS)
endRow = FindRow("Cash Flow", 2, DataTableWS)
TotRowsPage = TotRowsPage + endRow - startRow + 2
If (startRow > 0) And (startRow <= endRow) And (endRow <= LastRow)
Then
' Copy table from Excel
DataTableWS.Range("B" & startRow & ":M" & endRow).Copy
' Paste table into Word
With WordApp.Selection
.EndKey Unit:=wdStory
.TypeParagraph
.PasteExcelTable False, False, False
End With
' Set width of table
WordDoc.Tables(1).Select
WordDoc.Tables(1).PreferredWidthType = wdPreferredWidthPoints
WordDoc.Tables(1).PreferredWidth = InchesToPoints(7.82)
End If
Call CleanUp(WordApp, DataWB)
Set WordDoc = Nothing
Set WordApp = Nothing
End Sub

 

[Alan]

The work-around for this problem is to specify the width in points
vice inches, as below:

WordDoc.Tables(1).PreferredWidth = 563

Everything works perfectly now.

Aaaaaargggggg!!!

Alan