I've been tasked with writing a "screen designer" tool in Excel to help alleviate the tedium of filling in form after form by our clients.
All was going swimmingly until I started on the great idea to have it transfer all the data from the client's design changes (approx 20 worksheets involved) into a large report in Word. This report would then be given to the design team to help generate the website a lot quicker.
The code seems to work in that variables stored on a worksheet called 'Results' are transferred to the Word document via Word bookmarks. Ignore the one copying a range of cells (unless you can fix that one) as it's a recent addition to the code and I know it doesn't work as of yet (Errors #13 or #1004).
The same error keeps on cropping up:
"Error 462" - it'll work the first time but will fail every single time thereafter. I've looked up various solutions in Google and they all say that I should be making sure I close the Word Object once I've finished with it. This I've made sure I'm doing.
My code is taken from various sources (the three books on VBA I've bought in the last month plus this site and a few others found by Googling):
Private Sub CommandButton2_Click() Dim details As String Dim template As String Set objWord = CreateObject("Word.Application") If Err.Number = 429 Then Set objWord = CreateObject("Word.Application") Err.Number = 0 End If On Error GoTo Finally ' The template file *must* be in the same folder as the UDP spreadsheet template = ThisWorkbook.Path & "\UDPTemplate.dot" ' Ask Word to create a new document based on the template objWord.Documents.Add template Set doc = ActiveDocument ' Just checking it's our template. No bookmarks = not our template If doc.Bookmarks.Count < 1 Then MsgBox "Template not launched - no bookmarks found!", vbCritical & vbOKOnly Exit Sub End If With doc ' Headers & footers .Sections(1).Headers(wdHeaderFooterPrimary).Range.Text = "UDP Design Document v1.0" .Sections(1).Footers(wdHeaderFooterPrimary).Range.Text = Range("Results!B5").Value & " for " & Range("Results!B3").Value ' Bookmarked areas .Bookmarks.Item("ClientName").Range.Text = Range("Results!B3").Value .Bookmarks.Item("WebsiteName").Range.Text = Range("Results!B5").Value .Bookmarks.Item("ProjectDate").Range.Text = Range("Results!B7").Value .Bookmarks.Item("ProjectVersion").Range.Text = Range("Results!B9").Value .Bookmarks.Item("ProjectManager").Range.Text = Range("Results!B11").Value ' *** This bit doesn't work - either error #13 or #1004 *** .Bookmarks.Item("ButtonsTable").Range.Text = Range("Buttons!A4:B29") End With ' Switch on Word objWord.Visible = True ' Exit with error code trap if required Finally: If Err.Number <> 0 Then MsgBox "An error occured during Word transfer" & vbCr & vbCr & "Error #" & Err.Number objWord.Application.Quit 'Close app Set objWord = Nothing Set doc = Nothing Else objWord.Application.Quit 'Close app Set objWord = Nothing Set doc = Nothing End If End Sub
As you can see the error handler at the end gives me a heads-up if any error occurs but regardless of which outcome (error or not), the program still works on the first run and crashes every other time after. I'm "closing down" Word correctly - but guess Word & Excel don't realise that.
Bound to be something simple I've overlooked but what is it?