Word Checkbox Help

[HighBomber]

Hello,

For work I have been asked to convert many word documents into new excel templates.

The macro I have written is working flawlessly. It runs in Excel.

First it opens the Excel template
Then opens all Word files on by one in the given directory
Copies the specific text into the appropriate Excel document cells
Saves the Excel document with the Word documents filename
Lastly it reopens the template and moves onto the next Word fileThe Word document is a series of tables, so the program just tabs to each cell and copies the highlighted text.

My problem is with the checkboxes in the word document. Only half of them have bookmark numbers so I can't use those to check their status. Is there a way I can check the status of the checkbox by highlighting over it with tab?

Thanks,

[fumei]

Why do the checkboxes not have bookmarks? Were they copied?

Why do you need the status of the checkbox? I assume you mean whether it is checked or not.

Are these legacy formfield checkboxes? So the Word document is protected for forms?

If you are using Tab (I assume you mean Selection) to move through the tables, there is undoubtedly a better way to do this. Can you post your code?

It very well may be possible to get the checkbox Result without using tab at all. Can you post a sample document?

[macropod]

A better way might be to ignore the whole question of whether a given formfield has a bookmark and simply process it explicitly. Or you could programmatically add your own bookmarks to the formfields that lack them.

[fumei]

I would concur with adding bookmark names to your formfields. It is better if they have names.

[HighBomber]

I am sorry but I will have to ask whether I can post an example document. Basically they are procedure forms, but when the template was created the original designer had no intention of them ever being parsed through. The forms are not locked in anyway so the users of this template have taken liberties over the years (adding rows and columns) to fit their needs. These changes mean that not all documents are 100% the same and using tab (selection) to dynamically read the document tables has been the most successful. There could be a better way but in the mean time my application can read hundreds of these documents despite small changes.

Except for these check boxes.

The check boxes represent yes/no information in the form.

I will make sure the new Excel template checkboxes have bookmarks and are locked. It is quite possible the original template had bookmarked check boxes but that information was lost over the years.

If I could read the check boxes by the order they appear in the document I could guarantee what they represent. That is why I would like to select them in that fashion.

[macropod]

With the document, or at least the tables, are you extracting data from anything other than formfields? If so, how are the other data delineated? Is there anything else in the same cells as the checkboxes whose state you want to capture?

Although giving the formfields bookmark names might be the ideal, that should be done when the forms are created, not when you're trying to extract the data. The overheads in doing so at extraction time, especially if the documents have forms protection, are probably more effort than the results justify.

Here's a simple demo of getting the contents/values of all formfields in a document:
Sub GetData()
Dim FFld As FormField, StrTxt As String
StrTxt = "|"
With ActiveDocument
For Each FFld In .FormFields
With FFld
If .Type = wdFieldFormCheckBox Then
StrTxt = StrTxt & .CheckBox.Value & "|"
Else
StrTxt = StrTxt & .Result & "|"
End If
End With
Next
End With
MsgBox StrTxt
End Sub

[fumei]

"The forms are not locked in anyway"

Really? If the forms are NOT protected, the formfields (checkboxes) can not work. If the document is not protected, the user can not check yes/no.

If they ARE using the formfiel checkboxes, then it must be protected.



Unless of course these are not in fact formfield checkboxes...

[HighBomber]

fumei I took your advice and I have found a better way to parse the document. I can also search the document for specific text (ie. a row name) and select right of that to pull the related and required text.

There are issues with this, however. My method of parsing before would highlight items I didn't know I had to retrieve. Remember this document was not locked and when employees needed created new documents from the template they took liberties such as adding rows. These rows still contain important information. I will need my original procedure to parse the data for any information which was unaccounted for and then the search procedure to specifically pull the information.

I am not sure what you mean by form feilds. The document is made up of several tables. The cells in each table contain text or occasionally checkboxes. The data delineated only by knowing which cell is next to the cell I search for.

I guess what I really need to know is whether I can highlight a checkbox to check its status?

Also how do I check whether or not they are form field check boxes? I know the document is not locked in any way.

[macropod]

I am not sure what you mean by form feilds. The document is made up of several tables. The cells in each table contain text or occasionally checkboxes.
...
Also how do I check whether or not they are form field check boxes? I know the document is not locked in any way.
Simply run the macro I posted. Within a second or so, you'll know exactly how many formfields are in the document. If there's none, all you'll get is a message box the '|'. If you have some checkbox formfields, you'll get a message box that includes entries like '|True|' and '|False|'.

[macropod]

I can also search the document for specific text (ie. a row name) and select right of that to pull the related and required text.

There are issues with this, however. My method of parsing before would highlight items I didn't know I had to retrieve. Remember this document was not locked and when employees needed created new documents from the template they took liberties such as adding rows. These rows still contain important information. I will need my original procedure to parse the data for any information which was unaccounted for and then the search procedure to specifically pull the information.
If your users could add rows (and columns and merge/split cells) willy-nilly, how are you going to determine which cells go where in your Excel workbook?:banghead: For all you know, added rows may contain information that is related to what's in the adjoining rows that you're looking for the information in. And what happens if they've deleted some rows/columns?:dunno Whoever designed the input document with such laxity needs a good lashing.:whip Coding around these vagaries could be a real challenge. I suspect the output files are going to need some careful post-export validation for each and every cell.

[HighBomber]

I don't believe it! macropod, your snippet of code prints out the form values by order which they appear in the document! Thank you.

Also it is difficult to explain, but through some logic I can solve almost all the issues you brought up. The only problem I have, for example, is if someone added a form field that exists on very few documents. If I am not looking for that field, it will not be copied. This will result in missing information. I still believe that is better than of incorrect information.

You are correct though, once all the documents are converted a sample will need to be reviewed. I am hopeful this process would have less issues than the human error involved with manually copying and pasting. Plus if there are conversion issues the old documents will always be available.

[macropod]

I don't believe it! macropod, your snippet of code prints out the form values by order which they appear in the document!
Why is that a surprise? I did say:

a simple demo of getting the contents/values of all formfields in a document
The code simply starts at the first formfield and works its way through the document until the last.

The only problem I have, for example, is if someone added a form field that exists on very few documents. If I am not looking for that field, it will not be copied. This will result in missing information.
With the code I posted, they will be copied. There is no logic in the code to say which formfields should be processed.

[HighBomber]

Sorry I am not surprised in your claim, I only thought for a second my checkboxes were not form fields. I also thought your code would find them all but by the order of the bookmarks (blank bookmarked checkboxes first). I see this was not the case.



With the code I posted, they will be copied. There is no logic in the code to say which formfields should be processed.

Yes exactly. I have to decide what in the form will be processed to the new template. It isn't easy since one in a hundred documents may have an added row that contains crucial information. The previous macro I developed has proven very good at finding outliers, though.

Finding these added rows isn't a bad process either. It will enable us to develop the new template which is 100% compatible to all scenarios. (for the time being)

I also promise I will have this document locked. If something new must be added to the template, all documents must be updated.

Thanks again.

[macropod]

For this kind of exercise, the real value of bookmarked formfields would only become apparent if you wanted to process them out of order. Even then, though, bookmarks aren't really necessary, since you could refer to them via their index number (which their relative position in the document).

[fumei]

I would still like to know how you can have useable formfield checkboxes without protection for forms.

[macropod]

Maybe the document was unprotected after the checkboxes had been updated. Given that users could modify the document by adding table rows & formfields, who knows just what's been going on. Maybe the users simply copied & pasted checked & unchecked checkboxes as needed. That would explain the lack of bookmarks.

[fumei]

Even if they pasted new formfields (thus no bookmark names), they still could not use the checkboxes - making true or false - unless THAT Section was protected. And if it IS protected, they can not paste into it in the first place.

[macropod]

Even if they pasted new formfields (thus no bookmark names), they still could not use the checkboxes - making true or false - unless THAT Section was protected. And if it IS protected, they can not paste into it in the first place.
Imagine a scenario in which a user fills in the protected form, but decides another row or two need to be added to the table, with checkboxes. So they unprotect the filled-in form, add a couple of rows to the table, then copy & paste into that row checkboxes from other rows that are already checked, or unchecked, as need be. The new rows end up with un-bookmarked checkboxes that are checked/not checked according to what the user copied. There's no need to use the checkboxes, as such, simply copy/paste one or more with the required state.

[Frosty]

You can use the checkboxes in an unprotected document after a fashion... you can be taught to right-click on the checkbox, choose properties > default value ("checked" or "not checked").

I've had end-users who thought this was the way you checked and unchecked the boxes.

[fumei]

Ummm, yes I understand how they could get new formfields copied and pasted. However, the OP wants to check the status of checkboxes, including one would assume the new ones.

Unless the document is protected again, there will be no user set status for the new checkboxes. So of what value is the "status", unless the user can make the checkbox true or false?

Are we saying the document is unprotected, new formfields put in...and then who cares about the status of the new ones?

My point is, unless the document is protected - and again the OP is saying it is not - the user can not set the checkboxes. And what is the point of that?

We do not KNOW that copied checkboxes are used to artificially make a state.

[fumei]

Frosty..seriously? Sheeesh.

[macropod]

Ummm, yes I understand how they could get new formfields copied and pasted. However, the OP wants to check the status of checkboxes, including one would assume the new ones.

Unless the document is protected again, there will be no user set status for the new checkboxes.[QUOTE]
WRONG! Try it for your self, then run the macro I posted.
We do not KNOW that copied checkboxes are used to artificially make a state.
Granted do not KNOW. My point is that there is an easy way it CAN be done. Indeed, IIRC, unless they're using Office 2007 or later, that's the most likely way it was done, since re-protecting the form after adding new checkboxes would have reset the formfields in the earlier versions.

[fumei]

"Unless the document is protected again, there will be no user set status for the new checkboxes. "

Sorry I meant to say that it can not BE set by the user. They can not use the formfield as a changeable formfield (in the normal clickable way).