Thanks jjsfreedman. I've made little progress tonight, and I'm wondering the following.
1. Is it possible to add the information directly to the Building block with out actually having to 'insert' it into the word document? Since this is creating a link to an excel file, the Fields.Add actually results in the opening and reading of the excel file.
2. As i am hoping to add 70+ Report tables to building blocks, I can't think of any good loop ideas, as they all link to various named ranges in excel. I'm thinking maybe create an array of the named ranges and loop through the array?
3. Maybe a better approach is to first add all the building blocks with Alt-F3 into the same Gallery and Catalog. Then I would just loop that Gallery/Catalog replacing the filepath and name?
Still not even sure if I'm taking the best approach. I'm trying to have 'dynamic' tables in a building block. The user would chose the appropriate Excel file, and all of the building blocks tables would then be linked to the new file.
Code:
Sub AddCustomBuildingBlock()
Dim objTemplate As Template
Dim objBB As BuildingBlock
Dim objRange As Range
Dim FilePath, FileName, ReportTable As String
'Set the FilePath and Name - For testing only. Will have user choose the file via input.
FilePath = "C:\\Data\\Samples\\"
FileName = "Sample WP.xlsm"
' Set the template to store the building block
Set objTemplate = Templates(Environ("AppData") & "\Microsoft\Word\STARTUP\Report Building Blocks.dotm")
'Create the Chart Link and Select the Range
ReportTable = "LINK Excel.SheetMacroEnabled.12 """ & FilePath & FileName & """ ""Cover Charts!Charts_Cover_Main"" \p"
Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, Text:=ReportTable, PreserveFormatting:=False
Set objRange = Selection.Range
objRange.MoveStart unit:=wdCharacter, Count:=-1
' Add the building block to the template
Set objBB = objTemplate.BuildingBlockEntries.Add( _
Name:="Title", _
Type:=wdTypeAutoText, _
Category:="General", _
Range:=objRange)
objRange.Delete
End Sub