Referencing FieldCodes in VBA

ptmuldoon · #1 01-06-2015, 05:12 PM

I'm trying to figure how to either reference or assign a variable to various filed codes like {AUTHOR}. How to do reference the { } brackets? I tried the CHR codes, but it didn't want to work. Any ideas?

Code:

Sub FCTest()
    Dim FC As String
    
    FC = "char(123) AUTHOR chr(125)"
    MsgBox FC
    

End Sub

jjfreedman · #2 01-06-2015, 05:39 PM

Um, not like that!

I guess you know that you can't just type braces around a field code in a document, because that doesn't make a working field. You have to use Ctrl+F9 or the Insert > Quick Parts > Field dialog.

Similarly, in VBA you can't just assign a string to make a field. You must use the ActiveDocument.Fields.Add method, passing it parameters that say where to put it and what kind of field it will be.

For example, to insert an {AUTHOR} field at the current insertion point, you would write

Code:

    ActiveDocument.Fields.Add Range:=Selection.Range, Type:=wdFieldAuthor

There are a couple of other optional parameters you can use. By default, a field inserted this way includes the \* MERGEFORMAT switch; if you don't want that, you include the parameter PreserveFormatting:=False. The other parameter is called Text, and it can contain any string you need to add to the field's keyword. For example, in an INCLUDETEXT field you need to insert the path\filename of the file to include, so you would write

Code:

ActiveDocument.Fields.Add Range:=Selection.Range, Type:=wdFieldIncludeText, _
        Text:=Chr(34) & "D:\\Documents\\eq.docx" & Chr(34), PreserveFormatting:=False

(The Chr(34) represents the double-quote mark, which you need if the path or filename includes any spaces. It's a good idea to include the quotes event if there aren't any spaces. The explicit path\filename in this statement could be replaced with a string variable, and you might not know in advance whether there are spaces in its value.)

ptmuldoon · #3 01-06-2015, 06:04 PM

Thanks. That's exactly what I was trying to learn.

I've been experimenting with trying to find how to create a dynamic Building Block that is based on a Field code value that the user will set. Specifically the user will set the File Path and File Name, and then I hope to have a set of building blocks that use those values.

In my current testing and learning, it appears the building block is being modified after a field update, and losing the Field code Doc Property, I gave it, thus becoming 'static' content.. So my current thought is to possibly recreate the building blocks when the user changes the file path and file name.

That make sense? I'll try and post back some sample code later in the week if I can. I'm still not sure if i'm taking the right approach yet.

The main goal is to have a set of building blocks of various tables that are OLE linked to Excel. And the user should be able to basically press a button, choose the new file, and then the building blocks with link to the tables in the new file.

ptmuldoon · #4 01-07-2015, 12:50 PM

ok, hitting a roadblock again. I'm trying to learn now how to add take the field code information and then to place/add into a building block.

The field code gets created. but I want to be able to have the field in a building block.

Code:

Sub AddCustomBuildingBlock()
    Dim objTemplate As Template
    Dim objBB As BuildingBlock
    Dim objRange As Range
    Dim FilePath As String
 
    ' Set the template to store the building block
    Set objTemplate = Templates("C:\Users\paul.muldoon\AppData\Roaming\Microsoft\Word\STARTUP\Report Building Blocks.dotm")
 
    FilePath = "LINK Excel.SheetMacroEnabled.12 ""C:\\Data\\Samples\\Sample WP.xlsm"" ""Cover Charts!Charts_Cover_Main"" \p"
    Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, Text:=FilePath, PreserveFormatting:=False
  
    ' Collapse the range, set the range, and add the text
    Selection.Collapse
    Set objRange = Selection.Range
 
    objRange.Text = "Building blocks for the technically challenged"
    
     ' Add the building block to the template
        Set objBB = objTemplate.BuildingBlockEntries.Add( _
        Name:="Title", _
        Type:=wdTypeAutoText, _
        Category:="General", _
        Range:=objRange)
 
End Sub

jjfreedman · #5 01-07-2015, 03:44 PM

When you collapse the Selection, that excludes the field that you just inserted, and in turn the field is excluded from objRange -- that's why it doesn't appear in the building block.

Replace this part of your code

Code:

    ' Collapse the range, set the range, and add the text
    Selection.Collapse
    Set objRange = Selection.Range
 
    objRange.Text = "Building blocks for the technically challenged"

with this:

Code:

    ' Collapse the range, set the range, and add the text
    Selection.Collapse
    Set objRange = Selection.Range
 
    objRange.Text = vbCr & "Building blocks for the technically challenged"
    objRange.MoveStart unit:=wdCharacter, Count:=-1

That works for me. If the "technically challenged" text appears for you before the chart instead of after it, you'd have to replace the MoveStart with MoveEnd and change the Count value from -1 to 1. (Just as when using the mouse or arrow keys, selecting just the opening or closing field marker automatically selects the entire field.)

ptmuldoon · #6 01-09-2015, 05:02 AM

Thanks jjfreedman. But I think I didn't explain it correctly.

Basically, i want to be able to place the FieldCode Link Information into the datablock.

I'm getting pounded with snow today, so hopefully I can do some more playing and learning in my 'down time' today

Code:

Sub AddCustomBuildingBlock()
    Dim objTemplate As Template
    Dim objBB As BuildingBlock
    Dim objRange As Range
    Dim FilePath As String
 
    ' Set the template to store the building block
    Set objTemplate = Templates("C:\Users\paul.muldoon\AppData\Roaming\Microsoft\Word\STARTUP\Report Building Blocks.dotm")
 
    'How Do I get his FilePath/Section of Link Field Code into the datablock instead of the text below.
    FilePath = "LINK Excel.SheetMacroEnabled.12 ""C:\\Data\\Samples\\Sample WP.xlsm"" ""Cover Charts!Charts_Cover_Main"" \p"
    Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, Text:=FilePath, PreserveFormatting:=False
  
    ' Collapse the range, set the range, and add the text
    Selection.Collapse
    Set objRange = Selection.Range
 
    objRange.Text = "Building blocks for the technically challenged"
  
     ' Add the building block to the template
        Set objBB = objTemplate.BuildingBlockEntries.Add( _
        Name:="Title", _
        Type:=wdTypeAutoText, _
        Category:="General", _
        Range:=objRange)
 
End Sub

jjfreedman · #7 01-09-2015, 02:42 PM

The code changes I suggested do put the LINK field inside the building block -- have you tried it?

The key point is that the Selection must include the LINK field (or the visible chart that appears when the field is updated) at the time you create the building block. Your original code and the copy of it in your latest post don't do that -- the LINK field is outside the Selection because you collapsed the Selection and didn't expand it again after adding text.

ptmuldoon · #8 01-09-2015, 05:22 PM

Quote:

Originally Posted by jjfreedman

The code changes I suggested do put the LINK field inside the building block -- have you tried it?

The key point is that the Selection must include the LINK field (or the visible chart that appears when the field is updated) at the time you create the building block. Your original code and the copy of it in your latest post don't do that -- the LINK field is outside the Selection because you collapsed the Selection and didn't expand it again after adding text.

I'll be honest, and hadn't tried what you posted till just now, and I'm still confused by how it works.

I guess you don't actually need to reference an actual variable to add to the building block? You reference a range instead?

jjfreedman · #9 01-09-2015, 05:37 PM

Quote:

Originally Posted by ptmuldoon

I guess you don't actually need to reference an actual variable to add to the building block? You reference a range instead?

That's correct. It's analogous to the way you create a building block manually, where you select some part of the document and press Alt+F3, then fill in the boxes in the dialog. Whatever is in the selected part becomes the value of the building block.

In VBA, the Range parameter is the equivalent of the selection in the document (except that when you use a range you don't visibly select anything).

ptmuldoon · #10 01-10-2015, 07:34 PM

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

jjfreedman · #11 01-11-2015, 02:46 PM

1. No, Microsoft has not provided any way to "edit" the contents of an existing building block, so your wish won't be granted.

The only way to modify an existing building block is to insert it in some document, change the necessary parts, select the whole content, and add the building block again with the same name. When you do this manually with Alt+F3, Word pops up a message asking whether to replace the existing building block, but VBA just does the replacement. Then you need to save the template to make the change permanent.

2. Yes, an array of the Excel named ranges is an efficient way to handle looping to create a lot of similar building blocks. Going a step further, you could have the macro create the array by reading the list of named ranges from the workbook's Names collection http://excel.tips.net/T003106_Using_...n_a_Macro.html. (When you work with Excel VBA objects in Word or another program, you need to click the Tools menu in the macro editor, click References, and check the box next to Microsoft Excel Object Model. When you use objects that have the same name in both Word and Excel, such as Range, you must qualify the data type in the Dim declaration, for example Word.Range or Excel.Range.)

3. That isn't going to help. See point 1.

Guessed · #12 01-11-2015, 02:48 PM

It doesn't sound like you are taking the best approach. I can't work out why you need building blocks at all if you already know how to build the content with code on demand.

Why not simply create a macro that the user can interact with to build the relevant content when they need it? What does the added complexity of building blocks add to the mix?

ptmuldoon · #13 01-11-2015, 06:39 PM

Quote:

Originally Posted by Guessed

It doesn't sound like you are taking the best approach. I can't work out why you need building blocks at all if you already know how to build the content with code on demand.

Why not simply create a macro that the user can interact with to build the relevant content when they need it? What does the added complexity of building blocks add to the mix?

You just made an excellent point. Since I already know all of the content and can build the code on demand, I really don't need to use building blocks. I'll have to think on setting up a UserForm or similar.

I think perhaps another 'better' approach would be to create a table in excel, and read that info into a multidimensional array in Word. And then possibly have the information available in a dropdown list. The user could then choose their item, and the relevant link code could be added. Something like

Chart Name | Link Code
Monthly Sales | { LINK ....blah blah }

I should have some free time tomorrow in my office to experiment. I'm not the strongest in working with arrays, but I'll start reading up more on them.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Using FieldCodes, AutoText, BuildingBlocks	ptmuldoon	Word	5	01-02-2015 01:33 PM
Auto referencing	SharkAttack	Word	5	02-17-2013 07:09 PM
Referencing	darksupernova	Word	4	11-08-2012 03:37 AM
Multiple referencing	SPRITEtoo	Word	3	01-29-2012 07:39 PM
Cross referencing	maltesedog	Word	0	02-13-2009 07:43 AM