Word Automation

[EA]

I have a problem with the code below, quite a strange one.

The sub procedure below is called from within Excel to Produce a stand alone
Word Document. This procedure is called x number of times depending on the
data. This codes runs fine on my PC, and did run without issue on my
colleague's PC.

However now on my colleagues PC I am getting a Run-time error '91' Object
varibale or with block variable not set. This is occuring on the line
marked "******". The very strange thing is the error does not occur on the
first or second pass through the code, but on the third, when the third
document is attempted to be created.

I am using exactly the same code on my PC without issue - can anyone explain
this...please [smile]

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Private Sub Invoice(Client, CCY, MonthText)
Dim appWD As Word.Application
Dim TheError As Integer
Err.Number = 0
On Error GoTo WordNotOpen
Set appWD = GetObject(, "Word.application")
WordNotOpen:
If Err.Number = 429 Then
Set appWD = CreateObject("Word.application")
TheError = Err.Number
End If
' appWD.Visible = True

On Error GoTo 0
FileLocation = ThisWorkbook.Path

Dim WDobj As Word.Document
With appWD
Set WDobj = .Documents.Add(FileLocation & "\Brokerage.dot")
"******"
With WDobj
If CCY = "USD" Then
.Bookmarks("GBPText").Range.Delete
.Bookmarks("EURText").Range.Delete
ElseIf CCY = "GBP" Then
.Bookmarks("USDText").Range.Delete
.Bookmarks("EURText").Range.Delete
Else
.Bookmarks("GBPText").Range.Delete
.Bookmarks("USDText").Range.Delete
End If
.Fields.Update
.Fields.Unlink
.SaveAs FileLocation & "\Invoices\" & MonthText & "\" & CCY &
"\" & Client & ".doc"
.Close SaveChanges:=wdDoNotSaveChanges
End With
End With
Set WDobj = Nothing
If TheError = 429 Then appWD.Quit
Set appWD = Nothing

End Sub

 

[NickHK]

Not sure about the actual error, unless it is to do with you and your
colleague having different versions of Word installed.

However, can you not simplify your code by separating the opening/closing of
Word from the invoice generation, as creating/quitting an instance of Word
is intensive. e.g. pseudo-code

Open Word, using own instance, forget GetObject
open your template in your hidden instance
Do while Excel info exist
edit bookmarks
.saveas [appropriate path/filename]
Loop
.close template savechange=false
quit word
set all to nothing

NickHK

 

[EA]

Thank you for suggesting another way to do what I am already doing without
issue.

What I was looking for was some insight into my problem - why on the third
iteration through the code, with the first two working successfully can I
not create the Word document object.

Anyone else any ideas?

EA

Not sure about the actual error, unless it is to do with you and your
colleague having different versions of Word installed.

However, can you not simplify your code by separating the opening/closing
of
Word from the invoice generation, as creating/quitting an instance of Word
is intensive. e.g. pseudo-code

Open Word, using own instance, forget GetObject
open your template in your hidden instance
Do while Excel info exist
edit bookmarks
.saveas [appropriate path/filename]
Loop
.close template savechange=false
quit word
set all to nothing

NickHK

I have a problem with the code below, quite a strange one.

The sub procedure below is called from within Excel to Produce a stand alone
Word Document. This procedure is called x number of times depending on the
data. This codes runs fine on my PC, and did run without issue on my
colleague's PC.

However now on my colleagues PC I am getting a Run-time error '91' Object
varibale or with block variable not set. This is occuring on the line
marked "******". The very strange thing is the error does not occur on the
first or second pass through the code, but on the third, when the third
document is attempted to be created.

I am using exactly the same code on my PC without issue - can anyone explain
this...please [smile]

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Private Sub Invoice(Client, CCY, MonthText)
Dim appWD As Word.Application
Dim TheError As Integer
Err.Number = 0
On Error GoTo WordNotOpen
Set appWD = GetObject(, "Word.application")
WordNotOpen:
If Err.Number = 429 Then
Set appWD = CreateObject("Word.application")
TheError = Err.Number
End If
' appWD.Visible = True

On Error GoTo 0
FileLocation = ThisWorkbook.Path

Dim WDobj As Word.Document
With appWD
Set WDobj = .Documents.Add(FileLocation & "\Brokerage.dot")
"******"
With WDobj
If CCY = "USD" Then
.Bookmarks("GBPText").Range.Delete
.Bookmarks("EURText").Range.Delete
ElseIf CCY = "GBP" Then
.Bookmarks("USDText").Range.Delete
.Bookmarks("EURText").Range.Delete
Else
.Bookmarks("GBPText").Range.Delete
.Bookmarks("USDText").Range.Delete
End If
.Fields.Update
.Fields.Unlink
.SaveAs FileLocation & "\Invoices\" & MonthText & "\" & CCY &
"\" & Client & ".doc"
.Close SaveChanges:=wdDoNotSaveChanges
End With
End With
Set WDobj = Nothing
If TheError = 429 Then appWD.Quit
Set appWD = Nothing

End Sub

 

[Cindy M.]

Hi Ea,

The sub procedure below is called from within Excel to Produce a stand alone
Word Document. This procedure is called x number of times depending on the
data. This codes runs fine on my PC, and did run without issue on my
colleague's PC.

However now on my colleagues PC I am getting a Run-time error '91' Object
varibale or with block variable not set. This is occuring on the line
marked "******". The very strange thing is the error does not occur on the
first or second pass through the code, but on the third, when the third
document is attempted to be created.

Any difference when Word is running vs. when the procedure has to quit and
start Word (again and again and again...)?

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail