Error VBA Automation from Excel (4158)

[Alex St-Pierre]

Hi !
I create a report in Word using Excel VBA. All Excel tables are copied into
Word and then formatted. The report is about 25 pages and countains 20
tables. If I execute the first part (about 10 tables) or the last part (about
10 tables), it works well. If I execute all the report, the report stop at
the following line (around table #16). Any idea?

Example #1 (executed from Excel):
Dim oXlRng As Range
Dim appWord As Word.Application
Dim docWord As Word.Document
Dim oRange As Word.Range
Set appWord = New Word.Application
appWord.Visible = True
Set docWord = appWord.Documents.Add
Set oXlRng = ThisWorkbook.Sheets(T(iTab).SheetName).Range("TableOutput")
....
ThisWorkbook.Activate
oXlRng.Copy
docWord.Activate
iP = docWord.Paragraphs.Count
Set oRange = docWord.Paragraphs(iP).Range
oRange.PasteExcelTable LinkedToExcel:=False, WordFormatting:=False,
RTF:=False 'This returns an error 4198 and the table is not copied. ???
'If I try to re-execute the line, it still gives the same error..

Example #2 (An other example that happens only when I put the 20 tables
together is the following):
iP = DocWord.Paragraphs.Count
Set oRange = docWord.Paragraphs(iP).Range
oRange.InsertAfter "MyText"
oRange.Borders(wdBorderTop).LineStyle = wdLineStyleSingle
oRange.Borders(wdBorderTop).LineWidth = wdLineWidth050pt 'Gives error
5843 outside limit since there's no line..?
oRange.Borders.DistanceFromTop = 4

Thanks!
Alex

 

[Jean-Guy Marcil]

Hi !
I create a report in Word using Excel VBA. All Excel tables are copied into
Word and then formatted. The report is about 25 pages and countains 20
tables. If I execute the first part (about 10 tables) or the last part (about
10 tables), it works well. If I execute all the report, the report stop at
the following line (around table #16). Any idea?

I am a bit confused. You state that the code runs fine until the nth table.
But the code you post seems to indicate that you have two different errors
from the get go...
Or, the errors you pointed out only happen after a while... Why two errors?
Which one comes first... Is it random?

Also, since you are using objects and range, why do you keep using Activate?
As long as your objects are explicitly declared and used appropriately, you
do not need to keep switching between Word and Excel by Activating. Also,
unless you are using properties or methods that rely on the document being
visible, the code will run faster if the Word application stays invisible.

It might alos help to insert the following line at the end of each loop:
docWord.UndoClear
to clear the undo stack, which has been linked to problems with macros
performing lots of editing operations on a document.

Finally, to make then code run marginally faster, use With blocks. As in:

Set oRange = docWord.Paragraphs(iP).Range
oRange.InsertAfter "MyText"
oRange.Borders(wdBorderTop).LineStyle = wdLineStyleSingle
oRange.Borders(wdBorderTop).LineWidth = wdLineWidth050pt
oRange.Borders.DistanceFromTop = 4

Set oRange = docWord.Paragraphs(iP).Range
With oRange
.InsertAfter "MyText"
With .Borders(wdBorderTop)
.LineStyle = wdLineStyleSingle
.LineWidth = wdLineWidth050pt
End With
.Borders.DistanceFromTop = 4
End With

Example #1 (executed from Excel):
Dim oXlRng As Range
Dim appWord As Word.Application
Dim docWord As Word.Document
Dim oRange As Word.Range
Set appWord = New Word.Application
appWord.Visible = True
Set docWord = appWord.Documents.Add
Set oXlRng = ThisWorkbook.Sheets(T(iTab).SheetName).Range("TableOutput")
....
ThisWorkbook.Activate
oXlRng.Copy
docWord.Activate
iP = docWord.Paragraphs.Count
Set oRange = docWord.Paragraphs(iP).Range
oRange.PasteExcelTable LinkedToExcel:=False, WordFormatting:=False,
RTF:=False 'This returns an error 4198 and the table is not copied. ???
'If I try to re-execute the line, it still gives the same error..

I tried the following code without any errors:

Sub Test()

Dim oRange As Range
Dim docWord As Document
Dim iP As Long

Set docWord = ActiveDocument
iP = docWord.Paragraphs.Count

Set oRange = docWord.Paragraphs(iP).Range
oRange.PasteExcelTable LinkedToExcel:=False, WordFormatting:=False, RTF:=False

End Sub

Since you snipped some of the code... It is hard to tell why you get the
error.
How do you loop through all the tables in the Excel Workbook?
Note that your code, as is, will replace the content of the last paragraph
by the table. And, since I do not code that will prevent this, all tables
will be joined (no space between them).

Try with my suggestions (no Activate, undo, invisible...)
But, of course, if you want to debug, you will need to make the app visible.

Example #2 (An other example that happens only when I put the 20 tables
together is the following):
iP = DocWord.Paragraphs.Count
Set oRange = docWord.Paragraphs(iP).Range
oRange.InsertAfter "MyText"
oRange.Borders(wdBorderTop).LineStyle = wdLineStyleSingle
oRange.Borders(wdBorderTop).LineWidth = wdLineWidth050pt 'Gives error
5843 outside limit since there's no line..?
oRange.Borders.DistanceFromTop = 4

I ran this code as is without any problems.
I guess it depends on the content of the last paragraph in the document...

 

[Alex St-Pierre]

Thanks a lot for the answer! The solution is to put the line:
docWord.UndoClear before the macro that create each table.

I had the same error last year (happens randomly) with paragraphformat, ...
when our report contained only 10 tables. At that time, I realized that it
happens only when there were other word files open. So, I replaced:
Set appWord = Word.Application
If appWord Is Nothing Then Set appWord = New Word.Application
by Set appWord = New Word.Application
and the problem never came back until I add the second part of the report
(the appendix). So, now, I have a permanent solution!

When I use appWord.visible = False, the report takes 2 minutes instead of 5
to be created. This is a real gain. I tried it before and I had problems with
table border formatting. But now, it seems to work well (probably because I
refer to object only)

unless you are using properties or methods that rely on the document being
visible, the code will run faster if the Word application stays invisible.

I use a lot of properties for each tables, how to know if there's something
that does work when appWord is hidden?

I will probably hide the form and put a box that will show the creation %.
If there's a bug inside the macro when appWord is hidden, how do you work
with appWord. Probably better to unhide it.

I'll have removed the line: ThisWorkbook.Activate and the macro works well.

Thanks a lot!
Alex

 

[Jean-Guy Marcil]

Thanks a lot for the answer! The solution is to put the line:
docWord.UndoClear before the macro that create each table.

I had the same error last year (happens randomly) with paragraphformat, ...
when our report contained only 10 tables. At that time, I realized that it
happens only when there were other word files open. So, I replaced:
Set appWord = Word.Application
If appWord Is Nothing Then Set appWord = New Word.Application
by Set appWord = New Word.Application
and the problem never came back until I add the second part of the report
(the appendix). So, now, I have a permanent solution!

When I use appWord.visible = False, the report takes 2 minutes instead of 5
to be created. This is a real gain. I tried it before and I had problems with
table border formatting. But now, it seems to work well (probably because I
refer to object only)

I use a lot of properties for each tables, how to know if there's something
that does work when appWord is hidden?

If yo do not use the Selection object you should be OK with the app being
invisible.
Also, proper testing should tell...

I will probably hide the form and put a box that will show the creation %.
If there's a bug inside the macro when appWord is hidden, how do you work
with appWord. Probably better to unhide it.

Use error trapping. When an error is detected, then, and only then, make the
app visible.

But, again, with thorough testing, errors should not occur all that often...
;-)

 

[Julian]

"Alex St-Pierre" wrote:

If yo do not use the Selection object you should be OK with the app being
invisible.
Also, proper testing should tell...

Slight difference in mileage here...

In my experience (Word 2002 on an XP PC, I think but possibly also under NT
on a rather large server workstation) keeping the app invisible *can* cause
"random" errors that do not occur if the application remains visible - which
is a shame and a pain in various bodily parts...

I would start with the app visible (turn off screenupdating if you want) -
then when it is working properly try it with the app invisible; if it still
works relaibly, good - if you get "random" errors with the invisible app
don't waste time trying to debug it... just bite the bullet and keep it
visible.

Problems with visibility specifically occurred for me when working with a
very large number of files (~100,000; don't ask!) to be processed
sequentially. Because of the overall complexity I had to pay careful
attention to memory usage (despite having as much memory as could be fitted
in the machine!) so as not to run out, so there might be an interaction
affecting stability between resource usage and application visibility - but
I was never able to nail it down so that's just an "informed" guess <g> But
the point stands - app invisibility *can* cause problems.

HTH

 

[Jean-Guy Marcil]

Slight difference in mileage here...

In my experience (Word 2002 on an XP PC, I think but possibly also under NT
on a rather large server workstation) keeping the app invisible *can* cause
"random" errors that do not occur if the application remains visible - which
is a shame and a pain in various bodily parts...

I would start with the app visible (turn off screenupdating if you want) -
then when it is working properly try it with the app invisible; if it still
works relaibly, good - if you get "random" errors with the invisible app
don't waste time trying to debug it... just bite the bullet and keep it
visible.

Problems with visibility specifically occurred for me when working with a
very large number of files (~100,000; don't ask!) to be processed
sequentially. Because of the overall complexity I had to pay careful
attention to memory usage (despite having as much memory as could be fitted
in the machine!) so as not to run out, so there might be an interaction
affecting stability between resource usage and application visibility - but
I was never able to nail it down so that's just an "informed" guess <g> But
the point stands - app invisibility *can* cause problems.

I have not written code for handling >100,000 documents...
In my more limited experience, the only problems I have had with Word being
invisible was when I wrote code using certain methods/properties associated
with the Selection object. Whenever I had those errors, if I could code
without the Selection Object (which I do now as a standard by always using
the Range object) then the error(s) went away.
Of course, there are some instances when you must use the Selection object
(when using the Information property for example), then, of course, keeping
the App invisble is not an option...