VBA code for converting all non-field square brackets to form field square brackets Word 2010

Topic

New Reply

Currently when updating documents I have to convert non-fielded square brackets quite a long winded way – insert and open a text form field into doc, insert left square bracket (repeat for right square bracket), highlight left fielded square bracket Ctrl H to insert into find then in replace insert ^c, replace all (repeat for right square bracket). This is really time consuming but I don’t know how to write a VBA code for it – could anyone help me please
Thanks
Shelley :confused:

Reply | Quote

Replies

Perhaps you could explain why you’re using these brackets & formfields at all. Better still, explain what you’re trying to achieve with this process. I can’t make sense of what you’re doing. Simply adding [ & ] to a document doesn’t create formfields.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi Paul, in UK legal precedent documents, square brackets are used where there might be more than one option to choose from (especially property documents such as lease, agreement for lease etc.) depending client requirements for a particular transaction, what might be ok for one client may not be the same requirement for another client, e.g. [The Seller][The Buyer] or [he][she] or [6][12] months etc. so these types of optional phrases are put within (currently manual) square brackets so the fee earner can accept/change/delete to suit each client (fee earners send through pdfs of the doc with their scribbled handwriting/crossing out to the document production unit). Our house style has recently changed in that all manual square brackets must be converted to fields so the dpu operator can then just F11 through the doc to locate each square bracket and amend accordingly to save time. We have 1000s of documents to update so was looking for a quick fix to this problem.

Reply | Quote

Hi

(fee earners send through pdfs of the doc with their scribbled handwriting/crossing out to the document production unit)

Wow, omg.
Thousands of docs quick

He / she/ his / hers, their, their’s, our. That must be a long list of optional terms.

Pay macropod.

Reply | Quote

Hi again
I have attached a document that I tested the routine against.
Too simple it seems.
Geof

Reply | Quote

If the aim were just to convert the text bounded by [ ] as per your example to formfields, that could be done with:

Code:

Sub Demo()
Application.ScreenUpdating = False
Dim Rng As Range, FmFld As FormField, StrTxt As String
With ActiveDocument
  .ActiveWindow.View.ShowFieldCodes = True
  With .Range
    With .Find
      .ClearFormatting
      .Replacement.ClearFormatting
      .Text = "[*]"
      .Forward = True
      .Wrap = wdFindStop
      .Format = False
      .MatchWildcards = True
      .Execute
    End With
    Do While .Find.Found = True
      StrTxt = .Text
      Set Rng = .Duplicate
      Set FmFld = .FormFields.Add(Range:=Rng, Type:=wdFieldFormTextInput)
      FmFld.Result = StrTxt
      .Collapse wdCollapseEnd
      .Find.Execute
    Loop
  End With
  .ActiveWindow.View.ShowFieldCodes = False
End With
Application.ScreenUpdating = True
End Sub

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Maybe macropod could be a fee earner for that one? 🙂

cheers, Paul

Reply | Quote

I am just a mere dpu operator – I have asked out IT dept to help but was told ‘we do not support word development’, yes I was baffled by that one to – would happily pay anyone willing to make my work life easier shame my company can’t say the same, that’s solicitors for you. Thanks for the responses though. Regards Shelley

Reply | Quote

Shelley
Can you post a silly sample doc ?
Geof

Reply | Quote

OK, but though you’re calling these fields formfields, there’s no indication they’re anything other than ordinary fields (e.g. DATE, PAGE, REF, etc. fields). If you’re using documents in which users are meant to input text, choose and option, etc., you should probably be using true formfields in templates with ‘filling in forms’ protection applied. Simply double-clicking on such a template will create a new document will all the fields in-situ and in which users can move between fields just by pressing the TAB key. Moreover, field coding in such forms can be used to generate dependent text, perform calculations, and so on, thus minimising the risk of error. No [ ] required – unless you really want them. See attached demo.

If you send such a form out to a client, upon return it’s a trivial exercise for the formfields to be locked or replaced with their inputs.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi laughsalot (what else can one do)!

F11 is out of date you would move to Content Controls but below addin DocuFill from CDEV Computing is excellent and means only the authors (fee earners) need to fill in the information ONCE or you can fill it in from the handwriting on the pdf you receive.

You can spend $US39 and become the Word Developer by using an addin from CDEV a UK business run by Chris Devrell. His addin Docufill will resolve the fee earner’s horror. What version of Word do you use? The fee earners can do the work themselves and you Shelley can just check the document and its formatting etc. Or if they still handwrite edits and pdf them to you, you can fill in the variables using this program – better yet buy your own copy and enjoy laughingalot for pleasure not out of wanting to pull your hair out (ouch)!

Surely you can give yourself a leg up to Word Developer and get the fee earners to authorise a few copies of the program (remember to laughalot)!

———————————————————————–
To Set-up documents/templates:
Open the document/template that you wish to have DocuFill operate against. Enclose each occurrence of a variable in square brackets Eg: [Client Name].

You can have variables in the Header and Footer areas of a document.

It is a good idea to make the variables descriptive so rather than say [date] use [date of appointment]

This is an example of marked-up text:
[Client Name] of [Client Address] signed the contract on [signing date].

And it is required that the user type his/her/their/its etc …. DocuFill has the facility to offer a drop-down list instead of a text box. To set this up, type the variable in the document together with the required list options in the following format (note the colon and commas)

[pnoun: his, her, their, its].

This will then appear as follows in the DocuFill dialog

pnoun: his
her
their
its

The below image is what the dialog window that pops up to allow you to fill in the variables in square brackets. There is an options setting for dropdown choices and how they are typed in the Word document. Download the trial and open a Word File New document from the template and it will show all text in square brackets. You hit next page to move through to the end of the variables. If the variable is more than once in the template you insert the same variable in all those places and it is filled in.

41625-Docfill-image

41627-Docufill-Sample-legal-document

This is an excellent program Shelley. Chris does bespoke work for many companies including Microsoft so I urge you to give the program a try.

Regards
Janine

Reply | Quote

F11 is out of date you would move to Content Controls

That’s hardly an accurate characterisation of F11, which works in all Word versions for moving between fields.

As for content controls, they don’t work on Macs or with Word versions prior to Word 2007 – and even content control checkboxes don’t work with Word 2007. Even if that were not a concern, you can’t perform automatic calculations or generate conditional output with content controls unless the document is a macro-enabled one and, since you can’t ensure macros would be enabled by the client (especially given the potential for macros to be used to do harm), one can’t even ensure such a document would be functional.

Since the documents are for use by clients who may be using Macs or PCs with Word 2007 & earlier, it’s best to stick with features supported by both platforms and all versions. Hence formfields and other fields.

As for DocuFill, that appears to be a US-centric product, but Shelley is in the UK. And, since the documents need to be sent to clients for them to fill in, I’d question whether DocuFill is applicable for this even in the US.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi Janine, I will certainly look into this – thanks for the advice, regards, Shelley

Reply | Quote

I would recommend investigating Content Controls too. Paul’s arguments against CCs are well founded but Shelley’s case may still suit Content Controls quite well.

The ‘fee earners’ appear to be in-house legal staff so Word 2007+ on Windows machines may not be a issue. Files are sent to clients in PDF form so their versions of Word are irrelevant. If the Word files were converted to doc format or opened by Mac versions of Word, the CCs will gracefully degrade to become fields so the content won’t be lost anyway. CCs do have some real advantages which fields don’t provide. Date Pickers are very slick for instance. Using CCs you could also create a single ‘client xml file’ and using a simple macro to embed that file into any of the 1000s of proforma documents to instantly create a customised document where all the relevant ‘fields’ are completed.

I haven’t used DocuFill but there is no need to use a paid tool when Greg Maxey provides an excellent CC tool for free. See http://gregmaxey.mvps.org/word_tip_pages/content_control_tools.html

Reply | Quote

Files are sent to clients in PDF form so their versions of Word are irrelevant.[/quote]
I see nothing in the previous discussion to suggest clients get PDFs. Shelley said only that:

fee earners send through pdfs of the doc with their scribbled handwriting/crossing out to the document production unit

The use of formfields would allow the clients to make choices in the form itself, rather than having the fee earners send through hard-copy with “scribbled handwriting/crossing out”.

If the Word files were converted to doc format or opened by Mac versions of Word, the CCs will gracefully degrade to become fields so the content won’t be lost anyway.[/quote]
IIRC, they become plain text, not fields, just like when you convert such a document to the .doc format. Granted, the displayed content isn’t lost, but the functionality is, and that’s what using formfields without the need for macros is about.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Using CCs you could also create a single ‘client xml file’ and using a simple macro to embed that file into any of the 1000s of proforma documents to instantly create a customised document where all the relevant ‘fields’ are completed.

Now I am curious Andrew. Can you provide more information re embedding simple macro with client xml data please Andrew?

CCs are just as much work as Formfields which would be text1 etc when converted and all duplicate (same data) fields a xref would need to be created and copied – just as much time as mapped CCs would take.

Regards
Janine

Reply | Quote

Paul
Yes, you are right. it looks like Shelley is saying that the fee earners write on paper copies and then scan this as PDFs for back-office staff to implement these changes to existing Word templates. She did not specify how files are sent to clients. However, regardless of how electronic copies are supplied to external clients, if those documents display ‘mass production’ evidence like fields OR CCs the client is right to feel devalued. Better to remove this type of evidence from electronic outputs if you want to charge top dollar for your documents.

IMO the use of formfields offers advantages over the use of Content Controls but in many situations, Content Controls are a better solution. Both provide a means of allowing edits that can be replicated through the document.

Janine
I prefer to use CCs because:
– they replicate instantaneously when linked (fields need to be updated),
– can be linked to embedded xml content (thus allowing easy export/import of customer-specific info)
– can be used to update common document metadata such as title, subject etc (fields can display document metadata but can’t be edited directly)
– can be nested in other CCs
– can contain rich text formatted information
– can provide a date picker etc

Vba code that might be used to embed xml information into a Word document can be seen in the following links:
https://msdn.microsoft.com/en-us/library/bb608627.aspx
https://msdn.microsoft.com/en-us/library/aa433521(v=office.12).aspx
http://gregmaxey.mvps.org/word_tip_pages/tinkering_with_CustomXMLParts.html

You can also embed an existing xml file into a Word document using the Word GUI. An example is shown in the first part of this youtube clip https://www.youtube.com/watch?v=OrriThs7m1s
When an xml file is loaded as a custompart in the document, CCs can be linked by right clicking on the relevant node in the XML Mapping pane. If the document already contained linked CCs to the same structured xml file then the act of loading the xml file would import the new xml data into the CCs placed in the document.

Reply | Quote

Better to remove this type of evidence from electronic outputs if you want to charge top dollar for your documents.[/quote]
Agreed, which is why is suggested unlinking the fields when they’re returned. Presumably you have in mind doing the same thing to the content controls??

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi Shelley
I have taken your request to mean that you want to Insert fields at the positions of the content enclosed in square brackets.
The routine I have posted is a proof of concept. The code is not rock solid.

[*]There is no error checking. This makes bug testing easier.
[*]Some duplicated blocks of code exist.
[*]Some may think of more efficient coding.

The routine loops searching for a “[“. Once found an inner loop seeks the partner “]”. The text between the brackets is selected and a field is created to enclose the [bracketed text] and square brackets. The existing text remains in place and is visible.

Use F11
When completed you can use F11 to move from field to field. When you type the desired response you replace the field completely, obliterating the field braces { } and the lively wdBrightGreen shading.
Assumptions

[*]All square [] brackets are correctly paired.
[*]All square [ ] brackets are used solely to enclose prompt options.
[*]You will get erratic results if an opening bracket exists without it’s closing partner.
[*]There is content enclosed in [ content] in the document
[*]Users will use this interactively on a single doument at a time

Future
It may be possible to use this concept as part of a batch processing mode of operation. However someone has to check the documents are correctly structured first anyway.
Preferences
I like to turn the option that shows shaded fields to ON. This has no bearing on the macro at all.
General Warning
Please use on a copy of a document for testing purposes. Preferably without a connection to the network.
Use in accordance with your company Information System policies.
Use at your own risk.
Code
Place the accompanying macro in a new module in a test document.

Code:

Sub createFieldPrompts()
'----------------------------------------------
'todo
'pull duplicate code to separate routine
'make suitable for batch mode
'document code
'----------------------------------------------
Dim intFound As Integer
Dim rng As Range
Dim bRng As Range
Dim cRng As Range
Set rng = Application.ActiveDocument.Content
rng.Collapse wdCollapseStart
intFound = 0
    With rng
        .Find.ClearFormatting
        .Find.Forward = True
        .Find.Text = "["
        .Find.Execute
        rng.Select
        Set bRng = rng
        Do Until Selection = "]"
            Selection.MoveRight
            If Selection.Bookmarks.Exists("EndOfDoc") = True Then
                MsgBox (intFound & Chr(32) & "fields created")
            Exit Sub
            End If
        Loop
        Selection.MoveRight unit:=wdCharacter, Count:=1
        Set cRng = Selection.Range
        bRng.SetRange Start:=bRng.Start, End:=cRng.End
        bRng.Select
        Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
        PreserveFormatting:=False
        bRng.HighlightColorIndex = wdBrightGreen
        bRng.Collapse wdCollapseEnd
    End With
    Do While rng.Find.Found = True
        intFound = intFound + 1
        rng.Find.Execute
        rng.Select
        Set bRng = rng
        Do Until Selection = Chr(93)
            Selection.MoveRight
            If Selection.Bookmarks.Exists("EndOfDoc") = True Then
                MsgBox (intFound & Chr(32) & "fields created")
                Exit Sub
            End If
        Loop
        Selection.MoveRight unit:=wdCharacter, Count:=1
        Set cRng = Selection.Range
        bRng.SetRange Start:=bRng.Start, End:=cRng.End
        bRng.Select
        Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
        PreserveFormatting:=False
        bRng.HighlightColorIndex = wdBrightGreen
        bRng.Collapse wdCollapseEnd
    Loop
End Sub

Hope it helps.
I am but an amateur and a pragmatist.
Geof

Reply | Quote

Hi Geof, thank you for your response, I will certainly give the code a go – I work on bog standard word 2010 docs not forms as thought in previous posts – I think we are quite behind in our technology at work and I am trying my hardest to get IT to sort better solutions out.

41636-Example-square-bracket-doc

I have attached an example of some articles of association. Most of our precedent documents have just been put together over the years and only the wording updated when law changes etc. so most of our docs are just stored in a precedent folder for each department, all very basic unfortunately.

Thanks for the code, will give it a go today.
Regards
Shelley

Reply | Quote

Hi
Just tested your sample.
Epic fail.
The chewing gum and No. 8 wire does not like the nested brackets .
I knew about the nested brackets.

It would be more work than its worth to make the docs comply by adjusting the brackets.
I learned a bit.
Geof

Reply | Quote

Hi Shelley,

You can generate the kind of field structure your example shows with the following macro:

Code:

Sub Demo()
Application.ScreenUpdating = False
Dim Rng As Range, i As Long, FmFld As FormField, StrBkMkNm As String
With ActiveDocument
  With .Content.Find
    .ClearFormatting
    .Replacement.ClearFormatting
    .Forward = True
    .Wrap = wdFindContinue
    .Format = False
    .MatchCase = False
    .MatchWholeWord = False
    .MatchWildcards = False
    .MatchSoundsLike = False
    .MatchAllWordForms = False
  End With
  For i = 91 To 93 Step 2
    Set Rng = .Range
    Rng.Collapse wdCollapseStart
    Set FmFld = .FormFields.Add(Range:=Rng, Type:=wdFieldFormTextInput)
    With FmFld
      .Result = Chr(i)
      StrBkMkNm = .Name
      .Cut
    End With
    With .Content.Find
      .Text = Chr(i)
      .Replacement.Text = "^c"
      .Execute Replace:=wdReplaceAll
    End With
    If .Bookmarks.Exists(StrBkMkNm) Then .Bookmarks(StrBkMkNm).Delete
  Next
End With
Application.ScreenUpdating = True
End Sub

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi Paul, I think you may have cracked this, the code in post #20 seems to have worked – only thing wrong is that they are converting to bold every time, but the code itself works – very much appreciated. Regards Shelley

Reply | Quote

the code in post #20 seems to have worked – only thing wrong is that they are converting to bold every time

You can fix that by inserting:
.Range.Font.Bold = False
before:
StrBkMkNm = .Name

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi Paul, have updated the macro from post 20 and it works. Thank you so much for your help on this. Regards, Shelley

Reply | Quote

Hi Paul, I have been using you code from post 22 and it works except that when I need to update the cross references by using F9 all the text in the fields disappears (please see attached).
41710-macro-doc

Reply | Quote

To use formfields, the document must have ‘filling in forms’ protection applied. If you do that and insert:
FmFld.CalculateOnExit = True
after:
FmFld.Result = StrTxt
You shouldn’t need to manually update the cross-references. The forms protection can be applied by inserting:
.Protect Type:=wdAllowOnlyFormFields, NoReset:=True
after:
.ActiveWindow.View.ShowFieldCodes = False

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Thanks for your response, unfortunately it does not work how I need it to work for our documents, but thank you so much for your help, much appreciated. Regards Shelley

Reply | Quote