Populating a Table using VBA.

[Robert]

Hello NG friends,
Can you please help this Newbie with two minor coding problems plus one
larger one?

1. Fragment-1 below works well but is far from elegant. Is there a more
economical way of generating Column Headers, please?

2. Fragment-2 doesn't work properly. The For - Next loop overwrites the
first column header set up in Fragment-1. How should I change the loop
to prevent that happening?

3. Columns 2,3 and 4 will be populated (starting in Row 2) using the
first three Items in the Collection of GetSpellingSuggestions for each
spelling error now shown in Column 1. (There will always be three
Suggestions, assigned to string variables called "cheat1, cheat2,
cheat3", even though some will remain empty.)

I have experimented without success to nest two For-Next loops so as to
display the (approx.) six sets of three Suggestions in my Table. Can
anyone please suggest a code framework that I might adapt?

Many thanks for any help.

Robert.

'FRAGMENT 1
With Selection
.Tables(1).Rows.Alignment = wdAlignRowCenter
.Tables(1).Select
.Font.Size = 12
.Collapse Direction:=wdCollapseStart

'Code below populates Column Headers in Row 1.
.TypeText " ERROR WORD "
.MoveRight Unit:=wdCell, Extend:=wdMove
.TypeText "SUGGESTION-1"
.MoveRight Unit:=wdCell, Extend:=wdMove
.TypeText "SUGGESTION-2"
.MoveRight Unit:=wdCell, Extend:=wdMove
.TypeText "SUGGESTION-3"
.MoveRight Unit:=wdCell, Extend:=wdMove
.TypeText " CORRECTION "
.MoveRight Unit:=wdCell 'Puts cursor in Col 1, Row 2. Necessary ??
End With


'FRAGMENT 2
oCount = 1
ActiveDocument.Tables(1).Rows(2).Select 'Doesn't solve problem !
For Each oCell In ActiveDocument.Tables(1).Columns(1).Cells
oCell.Range.Text = ActiveDocument.SpellingErrors(oCount)
oCount = oCount + 1
If oCount > i Then '(i = total no of errors in text)
Exit For
End If
Next oCell
'STILL PROBLEM: For - Next loop overwrites Column header in Row 1.

 

[Jonathan West]

Hello NG friends,
Can you please help this Newbie with two minor coding problems plus one
larger one?

1. Fragment-1 below works well but is far from elegant. Is there a more
economical way of generating Column Headers, please?

Yes, try this

With Selection
.Tables(1).Rows.Alignment = wdAlignRowCenter
With .Tables(1)
.Range.Font.Size = 12
'Code below populates Column Headers in Row 1.
.Cell(1, 1).Range.Text = " ERROR WORD "
.Cell(1, 2).Range.Text = "SUGGESTION-1"
.Cell(1, 3).Range.Text = "SUGGESTION-2"
.Cell(1, 4).Range.Text = "SUGGESTION-3"
.Cell(1, 5).Range.Text = " CORRECTION "
End With
End With

2. Fragment-2 doesn't work properly. The For - Next loop overwrites the
first column header set up in Fragment-1. How should I change the loop
to prevent that happening?

Change it to this. The extra code simply skips over the first cell in the
column without doing anything

'FRAGMENT 2
Dim bBody as Boolean
oCount = 1
For Each oCell In ActiveDocument.Tables(1).Columns(1).Cells
If bBody Then
oCell.Range.Text = ActiveDocument.SpellingErrors(oCount)
oCount = oCount + 1
If oCount > i Then '(i = total no of errors in text)
Exit For
End If
Else
bBody = True
End If
Next oCell

3. Columns 2,3 and 4 will be populated (starting in Row 2) using the
first three Items in the Collection of GetSpellingSuggestions for each
spelling error now shown in Column 1. (There will always be three
Suggestions, assigned to string variables called "cheat1, cheat2,
cheat3", even though some will remain empty.)

I have experimented without success to nest two For-Next loops so as to
display the (approx.) six sets of three Suggestions in my Table. Can
anyone please suggest a code framework that I might adapt?

Something like this ought to work

Dim iRow As Long
Dim iCol As Long
Dim oSpell As SpellingSuggestions
On Error Resume Next
With Selection.Tables(1)
For iRow = 2 To .Rows.Count
Set oSpell = .Cell(iRow, 1).Range.GetSpellingSuggestions
For iCol = 2 To .Columns.Count - 1
.Cell(iRow, iCol).Range.Text = oSpell(iCol - 1)
Next iCol
Next iRow
End With

It is possible that the word doesn't have three spelling suggestions. The On
Error Resume Next line caters for this - it causes there simply to be no
text added to the relevant cell.

--
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup
Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org

 

[Robert]

Hello Jonathan,
Thank you for such a prompt and complete answer. There is much to
ponder on here. (Put another way, that means that I'm still trying to
understand how your splendidly elegant code works. )

I shall be away for the rest of today, but I promise a report on its
success sometime tomorrow.

With grateful thanks,
Robert.

 

[Robert]

Hello again Jonathan,
Fragments 1 and 2 worked perfectly first time - truly fantastic. Many
thanks.

With the "Get Spelling Suggestions" routine, this fails with a RunTime
Error 5941 at the line

.Cell(iRow, iCol).Range.Text=oSpell(iCol - 1)

alleging that "The requested member of the collection does
not exist."

This is right in the middle of the double For - Next loop that gave me
too such trouble.
(I don't feel quite so bad about it now

Have you any further thoughts you might share?
Renewed thanks for all your efforts.
Robert.

 

[Jonathan West]

Hi Robert

Yes, I know about that error. That is why there is the On Error Resume Next
line in my code sample - so that the macro doesn't stop at that line and
just goes on to the next line - not inserting anything in the cell because
there is no spelling suggestion available to insert.



--
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup
Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org

 

[Robert]

Hello Jonathan,
Perhaps I have described the error inadequately. The point is that the
macro does stop at that line causing a Break to occur. Yesterday the
first error in my sample text was one having no spelling suggestion at
all. Would On Error Resume cope with that? (In my now aborted coding I
have the macro print "(no suggestion)" in Column 2 when this occurs.)
Today I have omitted that word - the first error now has only one
suggestion which IS printed in the table. But the macro fails to act on
the ResumeNext command for Columns 3 and 4.
I have now changed the first error to one known to have available three
suggestions or more. This prints all three perfectly, only failing on
the next Row which only has one. So it would seem that the problem is
just with the On Error statement.

I hope this helps your diagnosis a little.
Best Wishes and thanks again.
Robert.

 

[Jonathan West]

Hello Jonathan,
Perhaps I have described the error inadequately. The point is that the
macro does stop at that line causing a Break to occur. Yesterday the
first error in my sample text was one having no spelling suggestion at
all. Would On Error Resume cope with that? (In my now aborted coding I
have the macro print "(no suggestion)" in Column 2 when this occurs.)
Today I have omitted that word - the first error now has only one
suggestion which IS printed in the table. But the macro fails to act on
the ResumeNext command for Columns 3 and 4.
I have now changed the first error to one known to have available three
suggestions or more. This prints all three perfectly, only failing on
the next Row which only has one. So it would seem that the problem is
just with the On Error statement.

Show me the relevant part of the code as you currently have it


--
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup
Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org

 

[Robert]

Certainly. Here it is ...

'Code below displays Spelling Suggestions.
Dim iRow As Long
Dim iCol As Long
Dim oSpell As SpellingSuggestions
On Error Resume Next
With Selection.Tables(1)
For iRow = 2 To .Rows.Count
Set oSpell = .Cell(iRow, 1).Range.GetSpellingSuggestions
For iCol = 2 To .Columns.Count - 1
.Cell(iRow, iCol).Range.Text = oSpell(iCol - 1) ' PROBLEM HERE:
Run-time Error.
Next iCol
Next iRow
End With

I have not modified it in any way. Thanks for your continued interest.

Robert.

 

[Jonathan West]

So I can reproduce the exact conditions, can you tell me which word is in
the first column of the row where the problem hits?

Also, can you tell me where the cursor is at the time the macro is run?

--
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup
Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org

 

[Robert]

Yes, gladly.

The first error-word in Column 1 is "srtagne" (= strange). This
generates 3+ suggestions which all display correctly.

So the problem hits on the second word, "outrajusly"(=outrageously).
This only generates one suggestion, which is displayed properly, but
leaves the following two cells empty. I know from earlier work that
this error-word will only ever produce one suggestion.

Inserting an Exit Sub after the printing of the table grid and its
Column Headers (but nothing else) shows the IP to be to the left of the
first Header, Col 1, Row 1. (The cursor also returns there after the
Error Message has been banished and the macro has stopped.)

I do hope this is helpful. Thanks again for your patience.
Robert

 

[Robert]

Hello again Jonathan. Good News.
I happened to spot a posting earlier today, 1st November at 3.30 pm.
The title was "Why doesn't 'Resume Next' work?"

I have, within the VBE, reset the Error Handling from "Break on all
Errors", to
"Break on unhandled Errors" (Tools | Options | General ). This has
done the trick.
All the Suggestions are now displayed correctly within the Table.

You see, your code is quite as impeccable as you knew it was all along.
;-)

My apologies for not knowing about this setting. This is the first
time I have ever
even touched Error Handling.

May I ask you one final service? Where an error is so gross as not to
generate any Suggestions at all, I would like "(no suggestion)" to
appear in Column 2. Can you suggest a line to include in the loop to
accomplish this? I should be most grateful.

Many thanks for the perspiration you have expended on this problem.

 

[Robert]

I forgot to add that the posting referred to was not to this Newsgroup
but to
microsoft.public.word.vba.general

Regards
Robert.

 

[Jonathan West]

May I ask you one final service? Where an error is so gross as not to
generate any Suggestions at all, I would like "(no suggestion)" to
appear in Column 2. Can you suggest a line to include in the loop to
accomplish this? I should be most grateful.

Change the code so it is as follows

'Code below displays Spelling Suggestions.
Dim iRow As Long
Dim iCol As Long
Dim strSpell as String
Dim oSpell As SpellingSuggestions
On Error Resume Next
With Selection.Tables(1)
For iRow = 2 To .Rows.Count
strSpell = "(no suggestion)"
Set oSpell = .Cell(iRow, 1).Range.GetSpellingSuggestions
strSpell = oSpell(iCol - 1)
For iCol = 2 To .Columns.Count - 1
.Cell(iRow, iCol).Range.Text = strSpell
Next iCol
Next iRow
End With

The way this works is that the contents of the variable strSpell is now
inserted into the table cell. Each time round the inner loop, it is first
preset to "(no suggestion)", then set to the appropriate spelling
suggestion. If that assignment causes an error (ignored because of the On
Error Resume Next command), strSpell still contains "(no suggestion)", which
is then inserted into the table cell.


--
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup
Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org

 

[Robert]

Good Morning Jonathan,
I hardly dare tell you this after your splendid efforts on my behalf,
but this last, small modification has had a disproportionate effect.
Using the same sample text as before, the Table now shows "(no
suggestion)" in all three columns (2, 3 & 4) for all error-words except
the last one in the text, which is displayed correctly. For the others,
no Suggestions are displayed at all.

As I mentioned earlier, nested loops are very difficult.

Good Luck,
Robert.

 

[Jonathan West]

Good Morning Jonathan,
I hardly dare tell you this after your splendid efforts on my behalf,
but this last, small modification has had a disproportionate effect.
Using the same sample text as before, the Table now shows "(no
suggestion)" in all three columns (2, 3 & 4) for all error-words except
the last one in the text, which is displayed correctly. For the others,
no Suggestions are displayed at all.

As I mentioned earlier, nested loops are very difficult.

Good Luck,
Robert.

Bother. Even through I got fragments 1 & 2 right without bothering to test,
I should have looked more carefully at this. I can see what I did wrong -
I've put the strSpell assignments in the wrong place - in the outer loop not
the inner one. Try this

'Code below displays Spelling Suggestions.
Dim iRow As Long
Dim iCol As Long
Dim strSpell as String
Dim oSpell As SpellingSuggestions
On Error Resume Next
With Selection.Tables(1)
For iRow = 2 To .Rows.Count
Set oSpell = .Cell(iRow, 1).Range.GetSpellingSuggestions
For iCol = 2 To .Columns.Count - 1
strSpell = "(no suggestion)"
strSpell = oSpell(iCol - 1)
.Cell(iRow, iCol).Range.Text = strSpell
Next iCol
Next iRow
End With

--
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup
Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org

 

[Robert]

Thanks a million, Jonathan! This works perfectly. Now that you've
done the difficult part for me, I can get on with minor, presentational
matters. I have learned a tremendous amount from our exchanges and
have been taken into realms that I knew nothing of. That is the great
value of these Newsgroups, led by patient and generous people like
yourself. Long may they and you continue!

With heartfelt thanks,
Robert.

 

[Jonathan West]

Thanks a million, Jonathan! This works perfectly. Now that you've
done the difficult part for me, I can get on with minor, presentational
matters. I have learned a tremendous amount from our exchanges and
have been taken into realms that I knew nothing of. That is the great
value of these Newsgroups, led by patient and generous people like
yourself. Long may they and you continue!

With heartfelt thanks,
Robert.

Glad we got there in the end!


--
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup
Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org