Dealing with Tables in VBA

[Budget Programmer]

I have a lot of experience with Excel VBA, but don't know much about Word
VBA. Here's what I'd like to do:

Find a occurance of a text string "Data Filter Requirements" after the table
of contents. (If I have to find the second occurance of it, that's fine).
After that text, there are a variable number of tables. Within each table,
I'd like to:
Find the text in the second column of each row "No Filter Required". That
string is always the last line of the cell. If the checkbox just to the left
of, and on the same last line as, that string within the cell is checked,
then do nothing and go to the next row. If there's something there, copy
everything else in the cell (except for that last line) to an Excel
spreadsheet where I'll deal with it later.
I'd like to loop through every row (variable number of rows) in every table
(variable number of tables). I'd appreciate any help you could give.
I just don't know how to search for a table, determine how many rows there
are, how many tables there are, find a checkbox, select only the last line of
text in a cell, etc.
Many Thanks.

 

[Doug Robbins - Word MVP]

What type of checkbox is it? A checkbox formfield as used in a protected
document or something else?

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Budget Programmer]

Hi,
When I click on the checkbox, the border of the dialogue box says "Check Box
Form Field Options". There is no bookmark and the checkbox is enabled.
THanks

 

[Budget Programmer]

Hi,
When I click on the Checkbox, the border on the resulting dialogue box says
"Check Box Form Field Options". There is no Bookmark, and the checkbox is
enabled.
Many Thanks

 

[Doug Robbins - Word MVP]

OK, I am going to assume that they are checkbox formfields. However, a bit
of clarification of the following would help:

Find the text in the second column of each row "No Filter Required". That
string is always the last line of the cell. If the checkbox just to the
left
of, and on the same last line as, that string within the cell is checked,
then do nothing and go to the next row. If there's something there, copy
everything else in the cell (except for that last line) to an Excel
spreadsheet where I'll deal with it later.

1. Is it correct that the checkbox and the text "No Filter Required" are
in the same cell of the table?

2. If the answer to 1 is "Yes", are the checkbox and the text "No Filter
Required" in a paragraph by themselves. That is, if you click on the
Show/Hide (¶) button, is the line before the line containing the checkbox
terminated with a ¶?

3 What do you mean by "If there's something there"? Exactly where is
"there"? Are we now talking about something being in the second column of
the next row of the table? And, which cell is it for which everything but
the last line should be copied. Are we now talking about the cell that
contains the checkbox and the text "No Filter Required"?


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Budget Programmer]

Hello,
The column layout of each row is:
Column 1 - A unique identifier for each row. Format is MB.01, MB.02, etc.
Column 2 - Variable contents. The only thing consistent about each cell in
column 2 is that there's a checkbox, a space, and the text "No Filter
Required"
Column 3 - It's comments for the reader. No value to this programming
effort. I'm only focusing on column 2

To answer your questions specifically:
1. Yes it's correct. The checkbox, a space, and "No Filter Required" are
the last line of the cell"
2. Yes, the line before each row is terminated with a paragraph indicator (¶)
3. The contents of the cell's are very variable.
The contents of the cell in column 2, row 2 is
"(checkbox)(space) Customer Number _____________¶
¶
(checkbox)(space)No Filter Required"

The contents of the cell in column 2, row 3 is:
"(checkbox, space)Active¶
(checkbox, space)Inactive¶
(checkbox, space)No Filter Required¶"

The contents of the cell in column 2, row 4 is:
"StartDate = yyyy/mm/dd¶
¶
(checkbox, space)No Filter Required¶"

Thanks again for your help.

 

[Doug Robbins - Word MVP]

Sorry, that does not really indicated what data you want copied to Excel.

Using that example, can you indicate what it is?

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Budget Programmer]

Hi,
Sorry it isn't clear.
Anytime the checkbox next to "No FIlter Required" is checked, do nothing for
that row.
Anytime the checkbox next to "no Filter Required" is NOT checked, copy
everything in that cell (except for the last line, containing the checkbox
and "No Filter Required")
To excel.
Thanks

 

[Doug Robbins - Word MVP]

The following code will display in a message box, the contents of the text
n-1 paragraphs in any cell in the second column of any table after the
second instance of "Data Filter Requirements" in the document where the nth
paragraph of that cell contains an unchecked checkbox formfield and the text
"No Filter Required"

I am not sure really where you want the information in Excel, so I will
leave that part up to you. However, you may want to take a look at the
article "Control Excel from Word" at:

http://www.word.mvps.org/FAQs/InterDev/ControlXLFromWord.htm


Dim Source As Range, SourceData As Range
Dim i As Long, j As Long, k As Long
i = 0
With ActiveDocument
Selection.HomeKey wdStory
Selection.Find.ClearFormatting
With Selection.Find
Do While .Execute(Findtext:="Data Filter Requirements",
Forward:=True, MatchWildcards:=False, Wrap:=wdFindStop) = True
i = i + 1
If i = 2 Then
Set Source = Selection.Range
Exit Do
End If
Loop
End With
Source.End = .Range.End
End With
With Source
For i = 1 To .Tables.Count
With .Tables(i)
For j = 1 To .Rows.Count
With .Rows(j).Cells(2).Range
k = .Paragraphs.Count
If InStr(.Paragraphs(k).Range, "No Filter Required") > 0
Then
If .Paragraphs(k).Range.FormFields(1).CheckBox.Value
= False Then
Set SourceData = .Paragraphs(1).Range
SourceData.End = .Paragraphs(k - 1).Range.End
MsgBox SourceData.Text
End If
End If
End With
Next j
End With
Next i
End With

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Budget Programmer]

Doug,
Thanks. I think I'm most of the way there, with your help. There's one
remaining problem. I think it's with the statement "k = .Paragraphs.Count".
I followed it in the debugger and "k" never increments beyond 1.
When I click on the button ¶ on the tool bar in the Word document, I see the
symbol ¶ after every line within the cell, so I'm assuming them to be what
Word considers a paragraph.
As a small test, I recorded a macro where I addded some text, pressed the
"Enter" key, added some text, pressed the "Enter" key, etc. It generated
the following code.
Selection.TypeText Text:="Text of Line 1"
Selection.TypeParagraph
Selection.TypeText Text:="Text of Line 2"
Selection.TypeParagraph
Selection.TypeParagraph

The ¶ symbol appeared 3 times in the text that was created by the little
test-macro I wrote, so it seems I have a Word paragraph indicator at the end
of every line.

Thanks for your help.

 

[Budget Programmer]

Doug,
Took another look at it. I gave you the wrong column number. The
"activity" is in column C. When I replaced the column indicator from a 2 to
a 3, everything worked fine.
Another thing I noticed. If there are 3 ¶ symbols, and one symbol that
looks similar to an asterisk at the end of the cell, the paragraph count = 4.
That's fine, I can deal with that. Just for my information, what's the
asterisk-like symbol at the end of the cell called? End-of-cell indicator?
Many thanks for all your help. I really appreciate it.
Phil

 

[Doug Robbins - Word MVP]

That is the end of cell marker and will be treated as a separate paragraph.
However, if the checkbox that you are looking for is on the previous line,
then you will need to use k - 1

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP