Run-time error '462' when I run macro more than once

[keaden]

Hello again all, I'm back again with a small problem that I'm encountering when I run the macro I have been tinkering with more than once.

I run the macro and it populates a word document for me with no problem, however when I close the test word documents and run the macro again I get a Run-time error '462'

I have done a bit of reading and I believe this error is generated by the word application not closing down and running in the background (although I'm not really very sure about how or why this would be occuring). If anyone has come accross this sort of error before or can see any glaring problems in my code I'd really appreciate a few pointers, thanks in advance. K

Private Sub CommandButton4_Click()
    Dim objWord As Word.Application
    Dim objDoc As Word.Document
    Dim objExcel As Object
    Dim theCombo As Control
    Dim x As Integer
    Dim y As Integer
    Dim Txt1 As String
    Dim Txt2 As String
    Dim Txt1Rplce As String
    Dim Txt2Rplce As String
    
    x = iAddedCount
    y = 1
    
    Set objWord = CreateObject("Word.Application")
    objWord.DisplayAlerts = False
    objWord.Visible = True
    objWord.Documents.Open ActiveWorkbook.Path & "\OSR Advisory Letter.dotm", ReadOnly:=True '"\test.dotx", ReadOnly:=True'"\Doc.dotm", ReadOnly:=True '"\test.dotx", ReadOnly:=True
    objWord.Activate
    objWord.Selection.Start = 1382
    
        For Each theCombo In OSRData.MultiPage1.Pages(2).Controls
                
                If x > 0 Then

                    objWord.Selection.FormFields.Add Range:=objWord.Selection.Range, Type:= _
                    wdFieldFormTextInput

                        With objWord.Dialogs(wdDialogFormFieldOptions)
                            .Name = ("Com" & y)
                            .Execute
                            objWord.Selection.ParagraphFormat.LeftIndent = InchesToPoints(0.63) 'not working throws an error when run a second time
                            objWord.Selection.Font.Bold = False
                            objWord.Selection.MoveRight
                            objWord.Selection.TypeParagraph
                            objWord.Selection.TypeParagraph
                        End With
                            
       
                    objWord.Selection.FormFields.Add Range:=objWord.Selection.Range, Type:= _
                    wdFieldFormTextInput
                    
                        With objWord.Dialogs(wdDialogFormFieldOptions)
                            .Name = ("Text1" & y)
                            .Execute
                            objWord.Selection.ParagraphFormat.LeftIndent = InchesToPoints(0.63) 'this line does not seem to generate the error when the previous line is commented out
                            objWord.Selection.Font.Bold = True
                            objWord.Selection.MoveRight
                            objWord.Selection.TypeParagraph
                            objWord.Selection.TypeParagraph
                        End With
                     
                        
                    objWord.Selection.FormFields.Add Range:=objWord.Selection.Range, Type:= _
                    wdFieldFormTextInput
                    
                        With objWord.Dialogs(wdDialogFormFieldOptions)
                            .Name = ("Text2" & y)
                            .Execute
                            'objWord.Selection.ParagraphFormat.LeftIndent = InchesToPoints(0.63)
                            objWord.Selection.Font.Bold = True
                            objWord.Selection.MoveRight
                            objWord.Selection.TypeParagraph
                            objWord.Selection.TypeParagraph
                        End With
                        
                        
                        With objWord.ActiveDocument
                            Txt1 = Me("Text1" & y).Text
                            
                            Txt2 = Me("Text2" & y).Text
                            
                            Txt1Rplce = Replace(Txt1, vbLf, "")
                            
                            Txt2Rplce = Replace(Txt2, vbLf, "")
                            
                            objWord.ActiveDocument.FormFields("Com" & y).Result = Me("Combo" & y).Text
                           
                            objWord.ActiveDocument.FormFields("Text1" & y).Result = "Observations:" & vbCr & Txt1Rplce 'Me("Text1" & y).Text
                            
                            objWord.ActiveDocument.FormFields("Text2" & y).Result = "Action(s):" & vbCr & Txt2Rplce 'Me("Text2" & y).Text
                            
                        End With

                        
                End If
                
                    y = y + 1
                    x = x - 1
                    
       Next theCombo

End Sub

the offending line is the

objWord.Selection.ParagraphFormat.LeftIndent = InchesToPoints(0.63)

I have no idea why a formatting issue would throw an error, any ideas?

 

[Norie]

I don't see any code to either close/save the Word document or quit the Word application.

If you don't do that then there will be a 'ghost' instance of Word kicking about in the background.

Is there a reason you aren't closing the document or quitting Word?

 

[keaden]

I don't see any code to either close/save the Word document or quit the Word application.

If you don't do that then there will be a 'ghost' instance of Word kicking about in the background.

Is there a reason you aren't closing the document or quitting Word?

Hello Norie, thanks for the reply.

Basically, the macro generates a reply letter in word from an excel userform containing calculations etc.

The userform is being used for site surveys in the field so I have been trying to build it, the macros and associated templates in a way in which the userform only has to be populated once to avoid repeating the same data and possibly introducing errors.

I had planned on having the instance of word open with the draft letter displayed so that the user could proof read it and make any edits to the document before saving a .docx copy.

Maybe I should just have the macro save a named copy of the letter and close the instance of word instead? But I would rather have the user decide if the letter is suitable before saving it if possible.

The problem seems to occur after I open excel, run the macro once and I get the required document.

If I run the macro again with that instance of word still running in the background it generates the letter again no problem.

The error comes when I close all instances of the generated word document and try to generate another copy of the letter without any instances of word being open.

Is this a common error? I would have thought that the error would have been generated when an instance of word is already open but it appears to be the opposite way around, once the first document(s) is/are closed down manually by me and I try to generate a new one, that's when the error is thrown up.

Thanks,
K

 

[keaden]

I found the problem I think. It looks as though there was a problem with the InchesToPoints section of the offending line so I changed it to

objWord.Selection.ParagraphFormat.LeftIndent = 40

instead, seems to work ok, thought I'd post it in case anyone finds themselves in the same problem.