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
Display More
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?
Many thanks
Mike