 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Word / Programming / November 2005Populating a Table using VBA.
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. > 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 [quoted text clipped - 5 lines] > 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.  SignatureRegards 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 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. 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. 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. SignatureRegards 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 > Hello again Jonathan, > Fragments 1 and 2 worked perfectly first time - truly fantastic. Many [quoted text clipped - 15 lines] > Renewed thanks for all your efforts. > 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. > Hello Jonathan, > Perhaps I have described the error inadequately. The point is that the [quoted text clipped - 9 lines] > 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 SignatureRegards 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 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. 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? SignatureRegards 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 > Certainly. Here it is ... > [quoted text clipped - 16 lines] > > 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 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. I forgot to add that the posting referred to was not to this Newsgroup but to microsoft.public.word.vba.general Regards Robert. > 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. SignatureRegards 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 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. > Good Morning Jonathan, > I hardly dare tell you this after your splendid efforts on my behalf, [quoted text clipped - 8 lines] > 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 SignatureRegards 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 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. > Thanks a million, Jonathan! This works perfectly. Now that you've > done the difficult part for me, I can get on with minor, presentational [quoted text clipped - 5 lines] > With heartfelt thanks, > Robert. Glad we got there in the end! SignatureRegards 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
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.