Error 5941 Creating a new word document from Excel
I'm trying to create a new word document to document my Excel VBA macros. I'm having trouble adding the title page. I open word and open a new blank document. Then I record a macro while I add the title page and fill in the text boxes. The macro that is created looks like this:
Sub Macro2()
'
' Macro2 Macro
'
'
Application.Templates( _
"C:\Users\mkent\AppData\Roaming\Microsoft\Docu ment Building Blocks\1033\16\Built-In Building Blocks.dotx" _
).BuildingBlockEntries("Facet").Insert Where:=Selection.Range, RichText:= _
True
ActiveDocument.Shapes.Range(Array("Text Box 154")).Select
Selection.TypeText Text:="Main document titleKent's Document Subtitle"
ActiveDocument.Shapes.Range(Array("Text Box 153")).Select
Selection.TypeText Text:="The document abstract"
ActiveDocument.Shapes.Range(Array("Text Box 152")).Select
Selection.TypeText Text:="mkentburel@gmail.com"
End Sub
Then I delete all elements of the title page and execute the macro. Everything works great. So I copy and paste this macro into my Excel VBA macro. I modify it so that it points to the Word.Application instead of the Excel that it's now running in. My macro looks like this:
Option Explicit
Sub DocumentVBA2()
' ----------------------------------------------------------------------------------------------------------------------------------------------------------
' In order to compile and run this code, you must first extend the VB Editor by including a couple of libraries in the reference list.
' Fire up Excel and go into VBA. Now Select Tools -> References. Make sure that you have a check next to these two libraries:
' Microsoft Visual Basic for Applications Extensibility 5.3 and
' Microsoft Word 16.0 Object Library
' These are the two I use on my Microsoft 365 system. You may have lower level versions on your system. Earlier versions
' should work without problem. These references are remembered in the workbook so they do not persist from one
' workbook to another.
'
' This code does not work when the workbook you are reporting is open from a OneDrive directory. The path name to the saved Word
' file will be invalid in that case.
' ---------------------------------------------------------------------------------------------------------------------------------------------------------
' First step is to setup to word environment
Dim wdApp As Word.Application
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim codify_time As String
Dim filename As String
Dim template As String
Dim insertPoint As Word.Range
Set VBProj = ThisWorkbook.VBProject
Set wdApp = New Word.Application
With wdApp
.Visible = True
.Activate
' Create a new document
.Documents.Add
'
' This section adds the title page to the new document
'
.Templates("C:\Users\mkent\AppData\Roaming\Microso ft\Document Building Blocks\1033\16\Built-In Building Blocks.dotx") _
.BuildingBlockEntries("Facet").Insert Where:=.Selection.Range, RichText:=True
.ActiveDocument.Shapes.Range(Array("Text Box 154")).Select
.Selection.TypeText Text:="Main title" & vbCrLf & "Sub title for Kent"
.ActiveDocument.Shapes.Range(Array("Text Box 153")).Select
.Selection.TypeText Text:="This is the abstract for the document."
.ActiveDocument.Shapes.Range(Array("Text Box 152")).Select
.Selection.TypeText Text:="Kent Burel" & vbCrLf & "mkentburel@gmail.com"
' Time to save and close the word document
codify_time = Format(Now, "yyyymmdd_hhmmss")
filename = ThisWorkbook.Path & "\Excel VBA Code." & codify_time & ".docx"
.ActiveDocument.SaveAs2 (filename)
.ActiveDocument.Close
.Quit
End With
Set wdApp = Nothing ' We are done with word
End Sub
When I get to the Templates statement then I get this error:
Run time error '5941':
The requested member of the collection does not exist.
What am I missing?
Thanks for your help.
|