[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

limit text to 200 words - or - how do I remove the last word in a Word document

Posted on 2010-01-07
9
Medium Priority
?
299 Views
Last Modified: 2012-06-27
I'm actually using Excel to populate a Word document in order to count the words in a text field. I want to return only 200 words from the Word document to the Excel VBA. I'm attaching what I have already. The problem seems to be that "oSelection.Range.Words.last.Delete " doesn't do what I think it should. Is this the proper usage or is there another/better way to do this?

Thanks
' Excel 2002 Code
' --------------------
Dim oWord As Object
Dim oWordDocument As Object
Dim oSelection As Object

Set oWord = CreateObject("Word.application")
oWord.Visible = True
Set oWordDocument = oWord.documents.Add
Set oSelection = oWord.Selection
oSelection.typetext Trim(Cells(Selection.Row, iColumnToLimitWords).Value)

oSelection.WholeStory

' loop counting the words and then deleting one word at a time until the limit is reached
Do While oWord.ActiveDocument.Range.ComputeStatistics(0) >= iNumberOfWordsAllowed
    oSelection.WholeStory
    
    oSelection.Range.Words.last.Delete  ' THIS LINE SEEMS TO DO NOTHING

Loop

oWordDocument.Selection.WholeStory
oWordDocument.Selection.Copy

Cells(Selection.Row, iColumnToLimitWords).Value = ActiveWorkbook.Paste

Open in new window

0
Comment
Question by:GordJones
  • 4
  • 3
  • 2
9 Comments
 
LVL 14

Expert Comment

by:Tommy Kinard
ID: 26202908
I would reccomend that you do this, limit the information being sent to Word, only send 200 words.

HTH
dragontooth


Function KillGT200(Info As Variant) As String
    Dim HldData() As String
    HldData = Split(Trim(CStr(Info)), " ")
    If UBound(HldData, 1) > 200 Then ReDim Preserve HldData(200)
    KillGT200 = Join(HldData, " ")
End Function

usage:
oSelection.typetext KillGT200(Cells(Selection.Row, iColumnToLimitWords).Value)

Open in new window

0
 

Author Comment

by:GordJones
ID: 26202954
This has flaws in that when a sentence ends and a new one begins they could all be considered 1 word. The following example would only have 3 words since there are no spaces between "end.New"... only a special character of a linefeed:
The end.
New sentence.
 
0
 
LVL 14

Expert Comment

by:Tommy Kinard
ID: 26204088
This should fix you right up.

dragontooth


Do While oWord.ActiveDocument.Range.ComputeStatistics(0) >= iNumberOfWordsAllowed
    oSelection.WholeStory
     
 oSelection.Collapse Direction:=wdCollapseEnd

    oSelection.MoveEnd Unit:=wdWord, Count:=-1
    oSelection.Delete Unit:=wdWord, Count:=1
Loop

Open in new window

0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 14

Assisted Solution

by:Tommy Kinard
Tommy Kinard earned 200 total points
ID: 26204133
Another way much faster

dragontooth
Sub betterway()
    Dim mI As Long
    mI = ActiveDocument.Range.ComputeStatistics(0)
    If mI > 200 Then
        Selection.WholeStory
        Selection.Collapse Direction:=wdCollapseEnd
        Selection.MoveEnd Unit:=wdWord, Count:=-(mI - 200)
        Selection.Delete Unit:=wdWord, Count:=(mI - 200)
    End If
 End Sub

Open in new window

0
 

Author Comment

by:GordJones
ID: 26204556
works great but one problem :(
Selection.MoveEnd Unit:=wdWord, Count:=-(mI - 200)
This line is doing a "Words" count instead of using the  "ComputeStatistics(0)" words count  :(  So it is gettings special characters, etc included and ending up with more than 200 "real" words.
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 26205853
Yes. ComputeStatistics(0) returns a number less than the Words collection count.

A paragraph with date: "07/01/10¶" counts as six in the Words collection, but is only one in ComputeStatistics(0).

Also the last paragraph cannot be deleted.

See if this helps

 


Sub TwoHundredWords()
    Dim rng As Range
    Dim rng2 As Range
    
    Do Until ActiveDocument.Range.ComputeStatistics(0) <= 200
        Set rng = ActiveDocument.Range
        Set rng2 = rng.Words.Last
        Do While rng2.Text = Chr$(13)
            rng.End = rng2.Start
            Set rng2 = rng.Words.Last
            rng2.Delete
        Loop
    Loop
 End Sub

Open in new window

0
 

Author Comment

by:GordJones
ID: 26283244
That worked great!
Now hopefully the final problem... when I paste back into Excel, and the text contains paragraphs, the text is ending up in 2 cells instead of all in one as I would like it. Example:
"This is a test.
 
This is the second paragraph. "
When this Word text is pasted back into Excel each paragraph is inserted on a separate row :( How can I force Excel to past all the text into a single cell?
0
 
LVL 76

Accepted Solution

by:
GrahamSkan earned 1800 total points
ID: 26284649
You can replace the paragraph marks with spaces.
Sub TwoHundredWords()
    Dim rng As Range
    Dim rng2 As Range
    
    Do Until ActiveDocument.Range.ComputeStatistics(0) <= 200
        Set rng = ActiveDocument.Range
        Set rng2 = rng.Words.Last
        Do While rng2.Text = Chr$(13)
            rng.End = rng2.Start
            Set rng2 = rng.Words.Last
            rng2.Delete
        Loop
    Loop
    With ActiveDocument.Range.Find
        .Text = "^p"
        .Replacement.Text = " "
        .Execute Replace:=wdReplaceAll
    End With
 End Sub

Open in new window

0
 

Author Comment

by:GordJones
ID: 26284711
That's what I'm looking for, thanks. I'll be taking it a step furtur and instead of relacing with a space, I'll replace with my own special character. Then once the text is in the Excel cell I'll replace it back into being a paragraph break :)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
MS Outlook undoubtedly is the most widely used email client.Its user-friendliness, cost effectiveness, and availability with Microsoft Office Suite make it the most popular email application.  Its compatibility with Microsoft applications like Exch…
Office 365 is currently available in five editions. Three of them are for business use: Office 365 Business Essentials, Office 365 Business, and Office 365 Business Premium. Two of them are for home/personal use: Office 365 Home and Office 365 Perso…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question