Solved

From Access.. using word vba... gettting error 462

Posted on 2014-09-18
7
319 Views
Last Modified: 2014-10-06
Using this routine with late-binding to open a word document and "merge" [fields] with actual values.  The code has worked fine for years with Office 2003, 2007, 2010 but not 2013.  The code errors on the first reference to the docWord object.  

I've read every reference to this error on Experts Exchange, Stackoverflow and Microsoft.com - including http://support.microsoft.com/kb/189618

Here is the code:

I removed all "with block" usages per a prior posting which recommended that as a way to avoid this error.  That change did not help me.  

Private Sub btnGenerateDocument_Click()
Dim strDataSource As String
Dim rst As ADODB.Recordset
Dim strPath As String
Dim strName As String
Dim varTemp As Variant
Dim strTemp As String
Dim lngID As Long
Dim strSQL As String
Dim strFieldName As String
Dim docWord As Object
On Error GoTo Err_btnGenerateDocument_Click
strPath = getconstant("BookingDocumentPath")
lngID = Nz(lstGroupSaleDocuments.Value, 0)
strTemp = lstGroupSaleDocuments.Column(1)
strName = InputBox("Enter a Name for the Generated Document:", "New Document Name")
If Len(strName) = 0 Then
    MsgBox "The New Document name is blank.  Please try again.", , "Validation"
    GoTo Exit_btnGenerateDocument_Click
ElseIf right$(strName, 4) <> ".DOC" And right$(strName, 4) <> "DOCX" Then
    MsgBox "Application only supports Word Documents as final outputs.  Please try again.", , "Validation"
    GoTo Exit_btnGenerateDocument_Click
End If
strPath = Trim(strPath)
If right$(strPath, 1) = "\" Then
Else
    strPath = strPath & "\"
End If
strName = strPath & strName
strDataSource = "qryBookings"
On Error Resume Next
Kill strName
On Error GoTo Err_btnGenerateDocument_Click
FileCopy strPath & strTemp, strName
'*******
Set docWord = CreateObject("Word.Document")
Set docWord = docWord.Application.Documents.Open(strName)
'**************************************
Set rst = New ADODB.Recordset
strSQL = "SELECT ztblQueryFieldNames.* FROM ztblQueryFieldNames WHERE QueryName = '" & strDataSource & "'"
rst.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
rst.MoveFirst
lngID = Nz(txtIndex.Value)
DoCmd.Hourglass True
Do Until rst.EOF
    strFieldName = "[" & Nz(rst!FieldName) & "]"
    varTemp = Nz(DLookup(Nz(rst!qryFieldName), strDataSource, "Index = " & lngID), " ")
    If Len(CStr(Nz(varTemp))) <= 255 Then
        docWord.Application.selection.HomeKey Unit:=6
        docWord.Application.selection.Find.text = strFieldName
        docWord.Application.selection.Find.Replacement.text = CStr(varTemp)
        docWord.Application.selection.Find.forward = True
        docWord.Application.selection.Find.wrap = 1
        docWord.Application.selection.Find.Format = False
        docWord.Application.selection.Find.MatchCase = False
        docWord.Application.selection.Find.MatchWholeWord = False
        docWord.Application.selection.Find.MatchWildcards = False
        docWord.Application.selection.Find.MatchSoundsLike = False
        docWord.Application.selection.Find.MatchAllWordForms = False
        docWord.Application.selection.Find.Execute Replace:=2
    Else
        docWord.Application.selection.HomeKey Unit:=6
        docWord.Application.selection.Find.text = strFieldName
        docWord.Application.selection.Find.Replacement.text = ""
        docWord.Application.selection.Find.forward = True
        docWord.Application.selection.Find.wrap = 1
        docWord.Application.selection.Find.Execute
        If docWord.Application.selection.text = strFieldName Then
            docWord.Application.selection = CStr(varTemp)
        End If
    End If
    DoEvents
    rst.MoveNext
Loop
docWord.Application.Visible = True
docWord.Save
MsgBox "The document has been generated.", , "FYI"
Exit_btnGenerateDocument_Click:
    DoCmd.Hourglass False
    strDataSource = vbNullString
    Set rst = Nothing
    strPath = vbNullString
    strName = vbNullString
    Set varTemp = Nothing
    Set docWord = Nothing
    strTemp = vbNullString
    strSQL = vbNullString
    strFieldName = vbNullString
    lngID = 0
    Exit Sub
Err_btnGenerateDocument_Click:
    If Err.Number = 3464 Then
        Resume Next
    ElseIf Err.Number = 53 Then
        MsgBox "Application cannot locate the Document corresponding the Template you have selected.  Please check the Template directory and file names and try again.", , "Validation"
        Resume Exit_btnGenerateDocument_Click
    ElseIf Err.Number = 5854 Then
        MsgBox "The Field: " & strFieldName & " is too long.  Please limit the text to 255 characters and try again.", , "Validation"
        docWord.Application.Visible = True
        docWord.Save
        Resume Exit_btnGenerateDocument_Click
    Else
        MsgBox "Error Number: " & Err.Number & " - " & Err.Description, , "btnGenerateDocument_Click"
        Resume Exit_btnGenerateDocument_Click
    End If
End Sub

Open in new window


Please - any advice will be greatly appreciated.
0
Comment
Question by:CharlieF2
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 12

Expert Comment

by:James Elliott
ID: 40332229
What error do you get?

Have you added all appropriate references?

Alt+F11 to open VB Editor.
Click Tools Menu
Click References.

Ensure that you have Micorsoft Office 12.0 (or later) object library, and Microsoft Word 12.0 (or later) object library ticked.

Rgds
0
 
LVL 12

Expert Comment

by:James Elliott
ID: 40332231
Also try adding all relevant folders to your 'trusted locations' in the trust centre settings in both Access and Word.
0
 
LVL 1

Author Comment

by:CharlieF2
ID: 40332493
James - thanks very much for the suggestions.  Due to the fact that I distribute this application to multiple customers with different versions of Office installed - I have to use late-binding - I cannot include references to Office 12 or Word 12 etc.  The application has worked fine for years.  It just now isn't running with Office 2013.  I will add the trusted locations and see if that helps.  The error that I get is 462 - and Microsoft is aware of this error but I've made the changes that they recommend in this link http://support.microsoft.com/kb/189618.  I cannot figure out why this isn't working.  I'll let you know if adding the folders changes the outcome.  Thanks again!
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:CharlieF2
ID: 40332644
James - thanks again for your suggestions.  Unfortunately, even after adding all of the relevant locations to the trusted locations under both Access and Word - I'm still getting the Error 462.  Any thoughts?
0
 
LVL 16

Expert Comment

by:Sheils
ID: 40333693
Hi Charlie,

I notice that you are using the same variable for the document and the application. This may be causing the problem. I suggest the following code which I use in all version of access. Even if it is not, its always better to diferentiate between the application and the document.

Dim wdApp As Object
Dim wdDoc  As Object

On Error Resume Next
Set wdApp = GetObject(, "Word.Application") ' If word is already running getobject

If Err.Number <> 0 Then 'Word isn't already running create object
Set wdApp = CreateObject("Word.Application")
End If
 
On Error GoTo 0
 
Set wdDoc = wdApp.Documents.Add 'Open new document based on the normal.dot template
 
'Insert your codes to populate document here

wdApp.Visible = True 'Make application visible

wdDoc.SaveAs "path and name of file you want to save"

Open in new window


However, I also notice that you are calling at least 2 functions (getconstant and filecopy) in the code you supplied. Have you done a debugging using breakpoint to find exactly where the code is breaking?
0
 
LVL 1

Author Comment

by:CharlieF2
ID: 40333828
Hello Sheils,

Thanks for your input.  I have put in a break and found that the code is stopping on the first instance of the user of my object:

docWord.Application.selection.HomeKey Unit:=6

I will change my code according to your suggestion and let you know what I find out.  

Thanks again,

Charlie
0
 
LVL 16

Accepted Solution

by:
Sheils earned 500 total points
ID: 40334622
I believe that this confirms that the confusion between the document and the application is the issue.

Try

Dim wdApp As Object
Dim wdDoc  As Object

On Error Resume Next
Set wdApp = GetObject(, "Word.Application") ' If word is already running getobject

If Err.Number <> 0 Then 'Word isn't already running create object
Set wdApp = CreateObject("Word.Application")
End If
 
On Error GoTo 0
 
Set wdDoc = wdApp.Documents.Add 'Open new document based on the normal.dot template
wdApp .selection.HomeKey Unit:=6

Open in new window


NB: You can also use

Set wdDoc = wdApp.Documents.Add(strName)  'Open new document based on your template.

This eliminate the need for the copyfile code but you have to save the template document as .dotx or .dot
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question