Error 462 2

[Creosote65]

Hi all,

I'm back with a problem with which I was dealing a while ago. The following vba code initiates a Word session, performs a mail merge, prompts the user to save the output file, closes Word and returns to a previous menu. Everything runs fine the first time. However, if I don't quit and restart Access before attempting to reuse this function, I will get an error 462.

As far as I can tell, I have closed all sessions of Word, so I shouldn't get a conflict. I also have a little vb script file that detects hidden instances of Word. It doesn't show any open instances after the macro closes Word.

I can't figure what the problem might be. Any help will be appreciated.

-Creosote

1Private Sub cmbQuote_AfterUpdate()
2 Dim appWord As Word.Application
3 On Error GoTo ExportError
4 If IsNull(frmDocType) Then 'Prompt User if doc type is not chosen
5 MsgBox "You must choose an output type before choosing a quote.", vbExclamation, "No document type"
6 cmbQuote = Null
7 ElseIf frmDocType = 4 Then 'Output the quote to a text file and merge with a form letter
8 DoCmd.TransferText acExportMerge, , "qryExpAddendumHeaderEng", "c:\MailMerge\qryExpAddendumHeaderEng.txt"

9 'Open Word and create a new document
10 Set appWord = New Word.Application
11 appWord.Visible = True
12 appWord.Documents.Add Template:="P:\Template files\Addendum template EN.dot", _
13 NewTemplate:=False, DocumentType:=0
14 With ActiveDocument.MailMerge
15 .Destination = wdSendToNewDocument
16 .SuppressBlankLines = True
17 With .DataSource
18 .FirstRecord = wdDefaultFirstRecord
19 .LastRecord = wdDefaultLastRecord
20 End With
21 .Execute Pause:=False
22 End With
23 With appWord
24 '.ActiveDocument.Save noprompt:=False
25 .Documents("Document1").Activate
26 .ActiveDocument.Close savechanges:=False
27 .Quit True
28 End With
29 Set appWord = Nothing
30 DoCmd.OpenForm "frmMenuContrEng", acNormal
31 DoCmd.Close acForm, "frmChooseQuoteExpArchContrEng", acSaveNo
32
33 End If

34 ExportError:
35 If Err.Number = 462 Then
36 'Word is still open from previous export.
37 appWord.Quit False
38 Set appWord = Nothing
39 MsgBox "The export has failed. Please close Access and try again.", vbExclamation, "Export Error"
40 DoCmd.OpenForm "frmMenuContrEng"
41 DoCmd.Close acForm, "frmChooseQuoteExpArchContrEng", acSaveNo
42 Exit Sub
43 End If

44 End Sub

 

[RoyVidar]

Office automation can be rather frustrating at times.

I've browsed thru the code, trying to find any unqualified referencing...

Searching Microsoft for 462 gives You may receive the "Run-time error '-2147023174' (800706ba)" error message or the "Run-time error '462'" error message when you run automation code to control Word 97 and ater versions (the typo ater in stead of later is copy pasted) which recommends using createobject in stead of the way of instantiating you use.

Then some of the other thingies, the following link, says some of the same as the above, but do take a look at the snippets marked DON'T USE THIS!! -> use a document object, in stead of "appWord.Documents.Add..." - which is implicit referencing, and might create such errors INFO: Troubleshooting Error 429 When Automating Office Applications

Have you checked the Task Manager to see if an instance of Word exists?

Roy-Vidar

 

[Creosote65]

Thanks Roy-Vidar,

You advice has given me direction. However, I find myself having the same problem, even with the object references that I make. At one point, I reverted to instantiation, but I can't figure out where, or how to resolve it. I even tried to deal with it through error trapping, but it won'te resolve the problem. Again the only way to fix the problem is to quit Access, restart it and try the sub routine again.

Here is the whole script, if you see any code that contradicts your advice, please point it out.

Thanks,
Creosote

1 Private Sub cmbQuote_AfterUpdate()
2 Dim appWord As Word.Application
3 Dim docWord As Word.Document
4 Dim rngWord As Word.Range
5 Dim rstContractDetails As New ADODB.Recordset
6 Dim lngProjVol As Long
7 Dim strItemCode As String
8 Dim strDescription As String
9 Dim strSellingUOM As String
10 Dim lngConversion As Long
11 Dim curPropSel As Currency
13 On Error GoTo ExportError

14 If IsNull(frmDocType) Then 'Prompt User if doc typee is not chosen
15 MsgBox "You must choose an output type before choosing a quote.", vbExclamation, "No document type"
16 cmbQuote = Null
17 ElseIf frmDocType = 4 Then 'Output the quote to a text file and merge with a form letter
18 DoCmd.TransferText acExportMerge, , "qryExportHeaderEng", "c:\MailMerge\qryExpAddendumHeaderEng.txt"

19 'Open Word and create a new document
20 Set appWord = CreateObject("Word.application")
21 With appWord
22 .Visible = True
23 .Activate
24 .WindowState = wdWindowStateMaximize
25 End With
26
27 Set docWord = appWord.Documents.Open("P:\Template files\Addendum template EN.dot")

28 With docWord.MailMerge
29 .Destination = wdSendToNewDocument
30 .SuppressBlankLines = True
31 With .DataSource
32 .FirstRecord = wdDefaultFirstRecord
33 .LastRecord = wdDefaultLastRecord
34 End With
35 .Execute Pause:=False
36 End With
37 With docWord
38 .Close Savechanges:=False
39 End With

40 Set docWord = Nothing

41 Set rngWord = ActiveDocument.Tables(1).Rows.Last.Range

42 'Open the detail recordset
43 DoCmd.OpenQuery "qryExportDetailEng", acViewNormal, acReadOnly
44 rstContractDetails.Open "tblExportDetail", _
45 CurrentProject.Connection, adOpenStatic

46 'Place values in the table
47 With rstContractDetails
48 Do While .EOF = False
49 lngProjVol = .Fields("ProjVol")
50 strItemCode = .Fields("Item Code")
51 If IsNull(.Fields("Description")) Then 'End if the item description is null
52 With appWord
53 .Quit False
54 End With
55 Set appWord = Nothing
56 AppActivate "Microsoft Access"
57 MsgBox "Item code " & strItemCode & " is invalid. Please correct this information and retry."
58 DoCmd.OpenForm "frmMenuContrEng"
59 DoCmd.Close acForm, "frmChooseQuoteExpArchContrEng", acSaveNo
60 Exit Sub
61 End If
62 strDescription = .Fields("Description")
63 strSellingUOM = .Fields("Selling UOM")
64 lngConversion = .Fields("Smallest to Selling")
65 If IsNull(.Fields("PropSel")) Then 'End if the Proposed Selling Price is null
66 With appWord
67 .Quit False
68 End With
69 Set appWord = Nothing
70 AppActivate "Microsoft Access"
71 MsgBox "Item code " & strItemCode & " has no proposed pricing. Please correct this information and retry."
72 DoCmd.OpenForm "frmMenuContrEng"
73 DoCmd.Close acForm, "frmChooseQuoteExpArchContrEng", acSaveNo
74 Exit Sub
75 End If
76 curPropSel = .Fields("PropSel")
77 'Add a row to the table
78 With rngWord
79 .Tables(1).Rows.Add
80 End With
81 'Place values in the table
82 With rngWord.Tables(1).Rows.Last
83 .Cells(1).Range.Text = lngProjVol
84 .Cells(2).Range.Text = strItemCode
85 .Cells(3).Range.Text = strDescription
86 .Cells(4).Range.Text = strSellingUOM
87 .Cells(5).Range.Text = lngConversion
88 .Cells(6).Range.Text = Format(curPropSel, "Currency")
89 End With
90 .MoveNext
91 Loop
92 With rngWord
93 .Tables(1).Rows(2).Delete
94 End With

95 Set rngWord = Nothing
96 Set docWord = Nothing
97 Set appWord = Nothing
98
99 End With
100 End If

101 ExportError:
102 If Err.Number = 462 Then
103 'Word is still open from previous export.
104 MsgBox "The export has failed. Please try again.", vbExclamation, "Export Error"
105 With appWord
106 .Quit False
107 End With
108 Set rngWord = Nothing
109 Set docWord = Nothing
110 Set appWord = Nothing
111 DoCmd.OpenForm "frmMenuContrEng"
112 DoCmd.Close acForm, "frmChooseQuoteExpArchContrEng", acSaveNo
113 Exit Sub
114 End If

115 End Sub

 

[RoyVidar]

Some qualifying:
[tt]
Set rngWord = appWord.ActiveDocument.Tables(1).Rows.Last.Range[/tt]

I'd perhaps also consider the advice in one of the links on trying completely late bound (declare as objects).

After the successfull merge, loops etc, you're not closing Word (no document.close and no app.quit), could that be the reason?.

Perhaps check for existing instance of word, and use that if available?

[tt]on error resume next
Set appWord = GetObject(,"Word.application")
if err.number<>0 then
err.clear
' no existing instance of word, create one
Set appWord = CreateObject("Word.application")
if err.number<>0 then
' oups - is word installed?
err.clear
exit sub
end if
end if
on error goto <your error handler>[/tt]

- else, I don't know, I'm afraid

Roy-Vidar

 

[Creosote65]

Again, thanks R-V,

That totally did the trick. I added the appWord and my error 462 just disappeared. Your help was invaluable.

Creosote

 

[apestaart]

Hallo Creosote65,

Iam facing the same problems. I am interested to hear what did the trick, Adding Set rngWord = appWord.ActiveDocument.Tables(1).Rows.Last.Range??
Or the error check ?? Or maybe both??

Can you send me the final code dealing with this problem?
Can you help me?

Regards Apestaart

My code looks like this :


On Error GoTo ErrorHandler

Dim appWord As Word.Application
Dim docs As Word.Documents
Dim doc As Object
Dim strLetter As String
Dim strTemplateDir As String
Dim strLetterDir As String
Dim strDocID As String
Dim prps As Object
Dim strDate As String
Dim strWordDoc As String
Dim strString As String
Dim strGeachte As String
Dim strTav As String
Dim strMedewerker As String
Dim strSofinummer As String
Dim strGeboortedatum As String
Dim strAdres As String
Dim strPostcodeplaats As String
Dim strTelefoonr As String
Dim strTelefoonw As String
Dim strAanhefrel As String
Dim strEindArbeidproces As String
Dim strContactpersoonbedrijf As String
Dim strBedrijfsnaam As String
Dim strPlaats As String
Dim strPostcode As String
Dim strStraat As String


Dim rs As Recordset

strWordDoc = Nz(Me![Document].Value)
'MsgBox strWordDoc
Set ctl = Me![Document]
If strWordDoc = "" Then
MsgBox "Please select a document"
ctl.SetFocus
ctl.Dropdown
GoTo ErrorHandlerExit
End If
Nieuw_doc:
Set appWord = CreateObject("Word.Application")
' Set appWord = GetObject(, "Word.Application")
strDate = CStr(Date)


strTemplateDir = appWord.Options.DefaultFilePath(wdUserTemplatesPath)

strTemplateDir = strTemplateDir & "\Personal Documents\"

strLetter = strTemplateDir & strWordDoc & ".dot"
Debug.Print "Letter: " & strLetter

Set docs = appWord.Documents
docs.Add strLetter
Set doc = appWord.ActiveDocument 'Toegevoegd
appWord.Visible = True 'Toegevoegd
If docs.Count >= 1 Then
MsgBox ActiveDocument.Name
Else
MsgBox "No documents are open"
End If

Debug.Print " Pad van actief document=" & ActiveDocument.Path & ActiveDocument.Name
Set prps = appWord.ActiveDocument.CustomDocumentProperties
' MsgBox prps.Count


MsgBox ActiveDocument.Bookmarks.Count

'gemeenschappelijke bookmarks adresgegevens bedrijf en kop brief
With appWord.Selection
.Goto What:=wdGoToBookmark, Name:="Plaats"
.TypeText Text:=Nz(Me![TabBedrijven Subform].Form![Plaats])
.Goto What:=wdGoToBookmark, Name:="Medewerker"
.TypeText Text:=strMedewerker
.Goto What:=wdGoToBookmark, Name:="Sofinummer"
.TypeText Text:=strSofinummer
.Goto What:=wdGoToBookmark, Name:="Geboortedatum"
.TypeText Text:=strGeboortedatum
.Goto What:=wdGoToBookmark, Name:="Bezoekadres"
.TypeText Text:=Nz(Me![Bezoekadres]) & " " & Nz(Me![Postcode]) & " " & Nz(Me![Plaats])
.Goto What:=wdGoToBookmark, Name:="Eind_arbeidsproces"
.TypeText Text:=Nz(Me![Eind Arbeidsproc])
End With

Me("verzonden brieven").SetFocus
Rec = Me.[Verzonden brieven].Form.RecordsetClone.RecordCount + 1
DoCmd.GoToRecord , , acGoTo, Rec
Me![Verzonden brieven].Form![RelatieID] = Me!RelatieID
Me![Verzonden brieven].Form![Document] = strWordDoc
Me![Verzonden brieven].Form![Datum verstuurd] = strDate
Refresh


With appWord
.Visible = True
.Activate
.Selection.WholeStory
.Selection.Fields.Update
.Selection.MoveDown Unit:=wdLine, Count:=1
End With
strDocID = str(Me!RelatieID) & "-" & str(Me![Verzonden brieven].Form![ID])
strLetterDir = "E:\Geert\BenG\" & strWordDoc & strDocID

ActiveDocument.SaveAs FileName:=(strLetterDir)
MsgBox " Uw document is opgeslagen in :" & strLetterDir
ErrorHandlerExit:
Exit Sub

ErrorHandler:
If Err = 429 Then
MsgBox "Word is not running; open Word with CreateObject"
Set appWord = CreateObject("Word.Application")
Resume Next
Else
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume ErrorHandlerExit
End If
End Sub

 

[Creosote65]

Hi apestaart,

I can't really speak for your code (frankly I don't think I'm good enough to figure out what soemone else is trying to do...), but I know that my code has been bulletproof for the last 7 months by writing it out as follows:

    ElseIf frmDocType = 4 Then 'Output the quote to a text file and merge with a form letter
        DoCmd.TransferText acExportMerge, , "qryIntrfcEngExportHeaderEng", "c:\MailMerge\qryExpAddendumHeader.txt"
        
    'Open the header recordset
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qryExportContrNoEng", acViewNormal, acReadOnly
    rstContractHeader.Open "tblExportContrNoEng", _
        CurrentProject.Connection, adOpenStatic
    
    'Verify contract number
    
    With rstContractHeader
        If (IsNull(.Fields("ContrNo")) Or .Fields("ContrNo") = "") Then 'End if the value is null.
            MsgBox "The quote contains no contract number. Please enter this information and retry."
            DoCmd.OpenForm "frmMenuContrEng"
            DoCmd.Close acForm, "frmChooseQuoteExpArchContrEng", acSaveNo
            Exit Sub
        End If
    End With
        
        'Open Word and create a new document
        Set appWord = CreateObject("Word.application")
        With appWord
            .Visible = True
            .Activate
            .WindowState = wdWindowStateMaximize
        End With

        Set docWord = appWord.Documents.Open("\\montre1apo2\Public$\Template files\Addendum template EN.dot")
    End If
        With docWord.MailMerge
            .Destination = wdSendToNewDocument
            .SuppressBlankLines = True
            With .DataSource
                .FirstRecord = wdDefaultFirstRecord
                .LastRecord = wdDefaultLastRecord
            End With
            .Execute Pause:=False
        End With
        With docWord
            .Close Savechanges:=False
        End With
        
        Set docWord = Nothing
        
        Set rngWord = appWord.ActiveDocument.Tables(1).Rows.Last.Range

That's the whole routine. If I noticed correctly, you don't properly close down the Word application after you use it. Try inserting your equivalent of " Set docWord = Nothing", that should do the trick.

Good luck,
Creo!

 

[RoyVidar]

What is ctl? Where it is declared?

This is one possible culprit

[tt] Set docs = appWord.Documents
docs.Add strLetter
Set doc = appWord.ActiveDocument 'Toegevoegd
appWord.Visible = True 'Toegevoegd
If docs.Count >= 1 Then
MsgBox ActiveDocument.Name
Else
MsgBox "No documents are open"
End If[/tt]

1 - usage of unqualified reference (ActiveDocument)
2 - don't use the documents collection, rather use the word document object (doc)

[tt] Set doc = appWord.Documents.Add(strLetter) ' not actually tested, but...
appWord.Visible = True 'Toegevoegd
If appword.documents.Count >= 1 Then
'MsgBox appword.ActiveDocument.Name
' shouldn't it be
mesgox doc.name ' ?
Else
MsgBox "No documents are open"
End If[/tt]

Through the rest of the code, it seems you're referring to Activedocument or appword.Activedocument in stead of using the doc variable. Use doc in stead, samples:

[tt]MsgBox ActiveDocument.Bookmarks.Count <- unqualified reference, use
MsgBox doc.bookmarks.count

ActiveDocument.SaveAs FileName:=(strLetterDir) <- unqualifid reference, use
doc.SaveAs FileName:=(strLetterDir)[/tt]

Both of these, (and some of the previous mentioned things) have the potential of leaving an extra instance of Word in memory, different errors... (also mentioned in the links I provided, I think)

And yes, do release the objects.

Roy-Vidar

 

[apestaart]

Thank you Roy,

I changed ActiveDocument.SaveAs FileName:=(strLetterDir) into doc.SaveAs FileName:=(strLetterDir) as you suggested and the code is running.

The objects are released after being checked and printed, direct in Word.

Reagrds,

Apestaart