Error 462 but only second time I run macro

[FrankL]

Hi all I am looking for helo from anyone who can! Pleasse respond if you know!

I am writing a vba macro from a 2007 access database which creates a word document. I am using the DOCMD.OUTPUTTO method to create a word document from a table.

I use the getobject command to load word (with an error trap to create the obejct if not loaded). I access the created word file which has a table and add information in the word header. I then format the table to print a report.

I am coming to a problem when I try to resize the columns of the table.

The weird thing is that the program works the first time I call it from the access form (I have a command button that loads data selected from the form and calls the macro). However the second time I run it I get the following error message when I get to the command to resize the table:

error 462 remote server cannot be found or is unavailable"

The current command to resize the column I am using (I have tried three other ways to do it without clearing the bug) is:

tblCM.Columns(1).SetWidth ColumnWidth:=InchesToPoints(0.6), _ rulerstyle:=wdAdjustNone

where tblCM is a variable containing the table I am trying to resize. I have tried to ensure i am running an application quit command, a document close and even a reset command (although I suspect this is mute) but am unable to clear whatever flag is being set when the program first runs.

This is very frustrating and I am unable to find a good book on Word vba that goes in depth to these idiosyncacies. If you don't know how to fix this but have an idea as to why this may be occuring that would be beneficial to me too.

Please offer any advice that you can!

Frank

[fumei]

"although I suspect this is mute"

Do you mean moot?

More details required.

"the second time I run it" Why are you running a second time? What does that mean exactly? Running a second time with the same document? When and more importantly when, are you using any Quit? Are saving one document, then making a new one as the second running? Are you using the same instance of Word? Or are you making a new instance of Word...I hope not.

[FrankL]

"although I suspect this is mute"

Do you mean moot?

More details required.

"the second time I run it" Why are you running a second time? What does that mean exactly? Running a second time with the same document? When and more importantly when, are you using any Quit? Are saving one document, then making a new one as the second running? Are you using the same instance of Word? Or are you making a new instance of Word...I hope not.

Hi thanks for your response.

Sorry spelling I do mean moot

The program is tied to a button in an access database form. The Button runs a macro that takes the information from the form and then calls a function in a procedure.

The function uses a getobejct command to load word with an error trap to run a create object statement and then resume if word is not running. I was told by the book Access 2007 VBA by Helen Feddema that this code does not create multiple instances.

When I say a second time I mean I load the form select the data and then hit the macro button. The process loads and produces the word doc and saves it where I want it to and goes back to the form. It is not until I hit the macro button again that I get the error whcih always plugs at the same place.

I have added application.quit; doc.close and various other commands in an attempt to debug this problem.

My suspicion is that their is a flag that has been switched or a file that is still open that I need to close but I am unable to determine what this might be.

This is very aggravating as the macro functionalilty does work. It seems to work if you load the vba editor and hit the square reset button and then go back to access but I can't have that as something to instruct users to do.

Hope this makes it clearer.

[Tinbendr]

Try setting your external objects to Nothing.

Set objWord = Nothing

[FrankL]

Try setting your external objects to Nothing.

Set objWord = Nothing

Ok I went in at the close of the procedure and set every object to Nothing and that did not seem to fix the problem.

So - - so far - - No Dice!

Does anyone please have any idea why VBA does this . . . . . .

Frank

[Tinbendr]

Well, without code, we can only offer best guess.

[FrankL]

Well, without code, we can only offer best guess.

Here is the code in all its Terrible glory or infamy as may be . . .

It will work . . . once!:motz2:


Private Sub cmdPCMw_Click()
Forms("Produce Control Matrix by Subsidiary").Requery
Forms("Produce Control Matrix by Subsidiary").Recalc
ExportCMWord
Forms("Produce Control Matrix by Subsidiary").Controls("cboYear") _
.Value = ""
Forms("Produce Control Matrix by Subsidiary").Controls("cboLevel") _
.Value = ""
Forms("Produce Control Matrix by Subsidiary").Controls("cboProcess") _
.Value = ""
Forms("Produce Control Matrix by Subsidiary").Controls("cboSub") _
.Value = ""
Forms("Produce Control Matrix by Subsidiary").Controls("lblLevel") _
.Caption = ""
Forms("Produce Control Matrix by Subsidiary").Controls("lblProcess") _
.Caption = ""
Forms("Produce Control Matrix by Subsidiary").Controls("lblSub") _
.Caption = ""
Reset
End Sub



'========================================================================== =======
Public Sub ExportCMWord()
'------------------------------------------------------------
' Set default variables
'------------------------------------------------------------
Const strUseFile As String = "WorkingFileXXX.doc"
Dim WordReport As Object
Dim appWD As Word.Application
Dim docs As Word.Documents
Dim docCM As Word.Document
Dim tblCM As Table
Dim strTbl, strTblFile As String
Dim strQMak As String
Dim strRng As Range
Dim strSaveName As String
Dim strPrompt As String
Dim strTitle As String
Dim StrDefault As String
Dim chkReport, tstFileFnd As Boolean
Dim strUseName As String
Dim ErrLine As String
Dim StrWS As String
Dim strWSPath As String
Dim rCntr As Integer
Dim plcTxt As String
Dim varSubLI, varLevLI, varProLI As Integer
Dim txtSubLI, txtLevLI, txtProLI As String
'********************************************************************
'--------Check Run Settings
'********************************************************************
If Forms("Produce Control Matrix by Subsidiary") _
.Controls("tglTestOnly").Caption = _
"Show Only Testable Controls" Then
varTestOnly = True
Else
varTestOnly = False
End If
varYear = Forms("Produce Control Matrix by Subsidiary") _
.Controls("cboYear").Value
varLevel = Forms("Produce Control Matrix by Subsidiary") _
.Controls("cboLevel").Value
varProcess = Forms("Produce Control Matrix by Subsidiary") _
.Controls("cboProcess").Value
varSub = Forms("Produce Control Matrix by Subsidiary") _
.Controls("cboSub").Value
'------------------------------------------------------------
' Create the Control Matrix Table
'------------------------------------------------------------
On Error GoTo ErrorHandler
ErrLine = "ewc1000"
strTbl = "tmak Control Matrix"
strDMak = "dmak Control Matrix"
If varTestOnly Then
strQMak = "qmak Control Matrix Test Only"
Else
strQMak = "qmak Control Matrix"
End If
DoCmd.SetWarnings False
DoCmd.OpenQuery strDMak
DoCmd.OpenQuery strQMak
'------------------------------------------------------------
' Set File Path and Save Names
'------------------------------------------------------------
ErrLine = "ewc1100"
strWSPath = Application.CurrentProject.Path
StrWS = Mid(varSub, 1, 3) & " " & Trim(Str(varYear)) & " Level " _
& Mid(varLevel, 1, 1) & " Process " & Mid(varProcess, 1, 2) _
& " Control Matrix"
strSaveName = strWSPath & "\" & StrWS & ".doc"
strUseName = strWSPath & "\" & strUseFile
strTblFile = strWSPath & "\" & strTbl & ".doc"
'------------------------------------------------------------
' Check File does not exist
'------------------------------------------------------------
ErrLine = "ewc1200"
Kill strUseName
ErrLine = "ewc1202"
Kill strTblFile
ErrLine = "ewc1210"
tstFileFnd = True
chkReport = True
Name strSaveName As strUseName
If tstFileFnd Then
msgPrompt = "The file " & strSaveName & " already exists." & Chr(13)
msgPrompt = msgPrompt & Chr(13)
msgPrompt = msgPrompt & "Do You Wish to continue and overwrite this file? (Y/N)"
msgTitle = "W A R N I N G"
msg = MsgBox(msgPrompt, vbCritical + vbYesNo, msgTitle)
If msg = vbYes Then
chkReport = True
Else
ErrLine = "ecm1220"
Name strUseName As strSaveName
chkReport = False
End If
End If
If chkReport Then
' ***********************************
' * Export the query to Word
' ***********************************
ErrLine = "ewc1330"
ChDir strWSPath
DoCmd.RunMacro "OutputTCMtoDOC"
' -----------------------------------------------------------------------
' DoCmd.TransferText Transfertype:=acExportMerge, TableName:=strTbl, _
' FileName:=strSaveName
' Broken Code
' -----------------------------------------------------------------------
' *************************************************************
' * Open New Word Document and assign variable designations
' *************************************************************
ErrLine = "ewc1400"
Set appWD = GetObject(Class:="Word.Application")
Set docs = appWD.Documents
docs.Open FileName:=strTblFile
Set docCM = appWD.ActiveDocument
appWD.Visible = True
appWD.Activate
' docCM.Activate
' *************************************************************
' * Set Pageview and orientation
' *************************************************************
ErrLine = "ewc1420"
If appWD.ActiveWindow.ActivePane.View.SplitSpecial <> wdPaneNone Then
appWD.ActiveWindow.Panes(2).Close
End If
If appWD.ActiveWindow.ActivePane.View.Type = wdNormalView _
Or appWD.ActiveWindow.ActivePane.View.Type = wdOutlineView Then
appWD.ActiveWindow.ActivePane.View.Type = wdPrintView
End If
'
docCM.PageSetup.TogglePortrait
docCM.PageSetup.TopMargin = "0.75"
docCM.PageSetup.RightMargin = "0.5"
docCM.PageSetup.LeftMargin = "0.5"
docCM.PageSetup.BottomMargin = "0.5"
'------------------------------------------------------------
' Place Header Info
'------------------------------------------------------------
ErrLine = "ewc1440"
appWD.ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageHeader
docCM.Paragraphs.LineSpacingRule = wdLineSpaceSingle
appWD.Selection.ParagraphFormat.Alignment = wdAlignParagraphLeft
appWD.Selection.Font.Name = "Calibri"
appWD.Selection.Font.Size = 12
appWD.Selection.Font.Bold = wdToggle
txtSubLI = Forms("Produce Control Matrix by Subsidiary") _
.Controls("lblSub").Caption
If Len(txtSubLI) > 0 Then
txtSubLI = txtSubLI
Else
txtSubLI = "Error Getting the Subsidiary Name"
End If
appWD.Selection.TypeText Text:="File: " & vbTab & StrWS & vbTab _
& "Sub: " & txtSubLI
appWD.Selection.TypeParagraph
txtLevLI = Forms("Produce Control Matrix by Subsidiary") _
.Controls("lblLevel").Caption
If Len(txtLevLI) > 0 Then
txtLevLI = txtLevLI
Else
txtLevLI = "Error Getting Audit Level"
End If
appWD.Selection.TypeText Text:=vbTab & txtLevLI
appWD.Selection.TypeParagraph
txtProLI = Forms("Produce Control Matrix by Subsidiary") _
.Controls("lblProcess").Caption
If Len(txtProLI) > 0 Then
txtProLI = txtProLI
Else
txtProLI = "Error Getting the Process Description"
End If
appWD.Selection.TypeText Text:=vbTab & txtProLI
appWD.Selection.TypeParagraph
appWD.Selection.TypeText Text:=vbTab _
& "Control Matrix for the Year Ending December 31, " & varYear
appWD.Selection.TypeParagraph
appWD.Selection.TypeText Text:="Preparer: " & vbTab _
& "Reviewer: " & vbTab _
& "Subsidiary Approval: "
appWD.Selection.TypeParagraph
'------------------------------------------------------------
' Format Body
'------------------------------------------------------------
ErrLine = "ewc1460"
appWD.ActiveWindow.ActivePane.View.SeekView = wdSeekMainDocument
Set tblCM = docCM.Tables(1)
tblCM.AllowPageBreaks = True
'------------------------------------------------------------
' Format Column Width
'------------------------------------------------------------
ErrLine = "ewc1480"
tblCM.Columns(1).SetWidth ColumnWidth:=InchesToPoints(0.6), _
rulerstyle:=wdAdjustNone ' 0.6"
tblCM.Columns(2).SetWidth ColumnWidth:=InchesToPoints(0.8), _
rulerstyle:=wdAdjustNone ' 1.4"
tblCM.Columns(3).SetWidth ColumnWidth:=InchesToPoints(1.5), _
rulerstyle:=wdAdjustNone ' 2.9"
tblCM.Columns(4).SetWidth ColumnWidth:=InchesToPoints(0.8), _
rulerstyle:=wdAdjustNone ' 3.7"
tblCM.Columns(5).SetWidth ColumnWidth:=InchesToPoints(0.8), _
rulerstyle:=wdAdjustNone ' 4.5"
tblCM.Columns(6).SetWidth ColumnWidth:=InchesToPoints(0.6), _
rulerstyle:=wdAdjustNone ' 5.1"
tblCM.Columns(7).SetWidth ColumnWidth:=InchesToPoints(0.4), _
rulerstyle:=wdAdjustNone ' 5.5"
tblCM.Columns(8).SetWidth ColumnWidth:=InchesToPoints(1.5), _
rulerstyle:=wdAdjustNone ' 7.0"
tblCM.Columns(9).SetWidth ColumnWidth:=InchesToPoints(2), _
rulerstyle:=wdAdjustNone ' 9.0"
tblCM.Columns(10).SetWidth ColumnWidth:=InchesToPoints(0.6), _
rulerstyle:=wdAdjustNone ' 9.6"
tblCM.Columns(11).SetWidth ColumnWidth:=InchesToPoints(0.4), _
rulerstyle:=wdAdjustNone ' 10.0"
'------------------------------------------------------------
' Format Borders
'------------------------------------------------------------
tblCM.Select
appWD.Selection.Font.Name = "Times New Roman"
appWD.Selection.Font.Size = 9
tblCM.Rows.AllowBreakAcrossPages = True
With tblCM.Borders(wdBorderTop)
.LineStyle = wdLineStyleDouble
.LineWidth = wdLineWidth050pt
.Color = wdColorGray875
End With
With tblCM.Borders(wdBorderLeft)
.LineStyle = wdLineStyleDouble
.LineWidth = wdLineWidth050pt
.Color = wdColorGray875
End With
With tblCM.Borders(wdBorderRight)
.LineStyle = wdLineStyleDouble
.LineWidth = wdLineWidth050pt
.Color = wdColorGray875
End With
With tblCM.Borders(wdBorderBottom)
.LineStyle = wdLineStyleDouble
.LineWidth = wdLineWidth050pt
.Color = wdColorGray875
End With
With tblCM.Borders(wdBorderHorizontal)
.LineStyle = wdLineStyleSingle
.LineWidth = wdLineWidth050pt
.Color = wdColorGray875
End With
With tblCM.Borders(wdBorderVertical)
.LineStyle = wdLineStyleSingle
.LineWidth = wdLineWidth050pt
.Color = wdColorGray875
End With
'
ErrLine = "ewc1490"
With tblCM.Columns(1).Borders(wdBorderBottom)
.LineStyle = wdLineStyleDouble
.LineWidth = wdLineWidth050pt
.Color = wdColorGray875
End With
tblCM.Rows(1).HeadingFormat = True
'------------------------------------------------------------
' Wrapup
'------------------------------------------------------------
ErrLine = "ewc1500"
ActiveDocument.SaveAs FileName:=strSaveName, FileFormat:=wdFormatDocument, _
LockComments:=False, Password:="", AddToRecentFiles:=True, _
WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:=False, _
SaveNativePictureFormat:=False, SaveFormsData:=False, _
SaveAsAOCELetter:=False
appWD.Quit savechanges:=wdDoNotSaveChanges
End If
'--------------------------------------------------------------------
Set WordReport = Nothing
Set appWD = Nothing
Set docs = Nothing
Set docCM = Nothing
Set tblCM = Nothing
Reset
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Exit Sub
'====================================================================
ErrorHandlerExit:
If ErrLine = "ewc1400" Or ErrLine = "ewc1420" Or ErrLine = "ewc1440" _
Or ErrLine = "ewc1460" Or ErrLine = "ewc1480" _
Or ErrLine = "ewc1490" Or ErrLine = "ewc1500" Then
chkReport = False
If msgErr <> 91 And msgErr <> 462 Then
appWD.Quit savechanges:=wdDoNotSaveChanges
End If
' docs.Close
End If
Reset
Exit Sub
ErrorHandler:
If Err = 53 Then
If ErrLine = "ewc1210" Then
tstFileFnd = False
End If
ElseIf Err = 429 And ErrLine = "ewc1400" Then
Set appWD = CreateObject(Class:="Word.Application")
Else
msgErr = Err
msgPrompt = "Err Line: " & ErrLine _
& " Err No: " & Err.Number & " - " & Err.Description
msgTitle = "E R R O R"
msg = MsgBox(msgPrompt, vbCritical + vbOKOnly, msgTitle)
Resume ErrorHandlerExit
End If
Resume Next
'---------------------------------------------------------------------
End Sub
'========================================================================== =======

[Tinbendr]

I'm afraid I'm not going to be much help. I did research the 462 error, and other people have had similar problems. But unfortunately, there was no help for them either.

I can only offer a couple of suggestions that may or may not be relevant.

Change

Set appWD = GetObject(Class:="Word.Application")
Set docs = appWD.Documents
docs.Open FileName:=strTblFile
Set docCM = appWD.ActiveDocument

to

Set appWD = GetObject(Class:="Word.Application")
Set docCM = appWD.Documents.Open(FileName:=strTblFile)

You set and activate the doc in one line. Now the document is the Word object.

You might try closing the file first, then the application.

Change

appWD.Quit savechanges:=wdDoNotSaveChanges

to

docCM.Close savechanges:=wdDoNotSaveChanges
appWD.Quit

I've edited the code to use Range instead of Selection. If the code is suffering from a timing issue, this might help. (I've attached it as a text file. I forgot I don't have Access at home. :( But I couldn't really test it anyway since I don't have the database files. )

[TonyJollans]

The usual cause of this error is implicit instantiation of an application object by using unqualified references.

On a quick scan of your code I see one such that catches a lot of people out. InchesToPoints is a method of the Word Application and should be qualified.

Try changing all references to InchesToPoints to appWD.InchesToPoints

There may be another cause but this is at least a (necessary) first step.

[FrankL]

I've edited the code to use Range instead of Selection. If the code is suffering from a timing issue, this might help. (I've attached it as a text file. I forgot I don't have Access at home. :( But I couldn't really test it anyway since I don't have the database files. )

Thanks for your help.

[FrankL]

The usual cause of this error is implicit instantiation of an application object by using unqualified references.

On a quick scan of your code I see one such that catches a lot of people out. InchesToPoints is a method of the Word Application and should be qualified.

Try changing all references to InchesToPoints to appWD.InchesToPoints

There may be another cause but this is at least a (necessary) first step.

I'll try that although it not working the second time you initiate the macro is really a mystery to me. I am used to computer programs working or not working. When the code says maybe is when I get confused.

Thanks for your help!

[Tinbendr]

Try changing all references to InchesToPoints to appWD.InchesToPointsArrgggg!!!!!!!! :doh:
I had the same thing happen to me on some Word/Excel code. It would error out whenever I had a instance of Excel running. Posted the code and Tom Ogilvy pointed out a very smiliar problem. Those fully qualified external objects can haunt you.

[fumei]

Tony, if it executes correctly the first time, how is it possible to execute without error - if the issue is unqualified references? They would be unqualified the first time.....yes?

[TonyJollans]

This is total conjecture but could explain it:

The first time the method is encountered, the parent app (Excel in this case) somehow navigates to the correct entry point in Word, using knowledge it has from the referenced Word library and pointers it has to the Word app from the earlier instantiation.

To save having to work it all out again, the parent app saves the information - somewhere off-planet that never gets re-initialised.

Further references to the method, in the first execution, use the saved pointer and all is well.

The Word app is closed when the execution ends, if not before - but the saved pointer isn't cleared.

The next run through, the parent app realises it has a saved pointer and tries to use it - but now it points to somewhere that no longer exists, somewhere in an object that "cannot be found or is unavailable".

Thinking itself unable to resolve the issue, it throws up its hands in surrender, confusing the poor user - all as a result of trying to help.

I think that's about as long as I can spin out "I don't know" :)

[FrankL]

Arrgggg!!!!!!!! :doh:
I had the same thing happen to me on some Word/Excel code. It would error out whenever I had a instance of Excel running. Posted the code and Tom Ogilvy pointed out a very smiliar problem. Those fully qualified external objects can haunt you.

Tony, Tinbendr, Fumei,

Just to give you an update that did fix the problem.

Just so you know I had to place the appWD variable in front of the Activedocument.saveas command as well. You'll find that right after line ewc1500.

I set a variable called errLine with subsequent numbering so that the error message gives me sum idea where in the code the bug broke down without my having to break it and run through with f8 in the debugger.

Thanks greatly for all your help, this should improve all my future macros. I usually use VBA to Excel but my boss wants the programs changed to go to Word now so understanding this will save me time. - - Thanks

[TonyJollans]

Yes, ActiveDocument will suffer the same problem - I didn't notice that when I scanned the code. Glad you're sorted.

[fumei]

"the parent app saves the information - somewhere off-planet"

crack me up

An excellent, excellent, job of conjecture.