Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel using Word --- Object is not responding
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel using Word --- Object is not responding
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel using Word --- Object is not responding
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel using Word --- Object is not responding
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel using Word --- Object is not responding
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel using Word --- Object is not responding
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, -- Carlos Mallen "Alan" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel using Word --- Object is not responding
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel using Word --- Object is not responding
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel object linked to word will not add sums in word | Excel Discussion (Misc queries) | |||
Excel Object in Word Document | Excel Programming | |||
windows excel &word 2003 pro save as command not responding | Excel Discussion (Misc queries) | |||
can't save anything in excel or word .... i get "not responding" | Excel Discussion (Misc queries) | |||
word object in excel | Excel Programming |