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

--
Carlos Mallen


"Alan" wrote:

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