VBA code bug - I'm stumped

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Keri - 04 Nov 2005 19:35 GMT

I used the Word "Record a Macro" function to write this code for me. I
record the macro, and it all works fine. But when I try to run the macro
again, I get "Run Time Error 4605: 'This method or property is not available
because no text is selected'". The debugger has the "Selection.Copy" line
highlighted.

What is the problem?
Here is the code:
RTM4 Macro
' Macro recorded 11/4/2005 by Keri McConnell
'
Selection.Find.ClearFormatting
With Selection.Find
.Text = "T-?{35}"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchAllWordForms = False
.MatchSoundsLike = False
.MatchWildcards = True
End With
Selection.Copy
Documents.Add DocumentType:=wdNewBlankDocument
Selection.PasteAndFormat (wdPasteDefault)
Selection.HomeKey Unit:=wdStory
Selection.WholeStory
Selection.ConvertToTable Separator:=wdSeparateByTabs, NumColumns:=3, _
NumRows:=100, AutoFitBehavior:=wdAutoFitFixed
With Selection.Tables(1)
.Style = "Table Grid"
.ApplyStyleHeadingRows = True
.ApplyStyleLastRow = True
.ApplyStyleFirstColumn = True
.ApplyStyleLastColumn = True
End With
End Sub

Reply to this Message

Jay Freedman - 05 Nov 2005 04:10 GMT

You expect the macro to have selected a group of text before it
copies. This macro doesn't do that.

If you want the Find to actually find the text consisting of T-
followed by 35 characters, there must be a line that calls the
.Execute method of the Selection.Find method. You can do it this way:

....
.MatchSoundsLike = False
.MatchWildcards = True
.Execute ' <==== add this
End With
Selection.Copy

The recorder has a number of problems, including failing to record
certain things. See
http://www.word.mvps.org/FAQs/MacrosVBA/ModifyRecordedMacro.htm.

--
Regards,
Jay Freedman
Microsoft Word MVP FAQ: http://word.mvps.org

>I used the Word "Record a Macro" function to write this code for me. I
>record the macro, and it all works fine. But when I try to run the macro
[quoted text clipped - 35 lines]
> End With
>End Sub

Reply to this Message

Keri - 07 Nov 2005 14:35 GMT

Thanks Jay. This got me one step closer. Now, the macro runs. But it only
finds, copies, and pastes one occurence of the "T-" (followed by 35
characters). I will be reading your article to learn more about the
recorder, but can you tell me how to change the code so that I can select ALL
of the occurences of "T-" before copying and pasting?

Thanks
Keri

> You expect the macro to have selected a group of text before it
> copies. This macro doesn't do that.
[quoted text clipped - 58 lines]
> > End With
> >End Sub

Reply to this Message

Jay Freedman - 07 Nov 2005 19:35 GMT

Hi Keri,

Let's go for an almost complete rewrite. :-)

First off, although you can use the Ctrl key to select more than one piece
of text in the user interface, that function was never implemented in VBA
(http://support.microsoft.com/?kbid=288424). In a macro, you have to find
each individual occurrence and transfer it to the new document. You also
need a paragraph mark at the end of it, unless you know that the last
character in the copied text is a paragraph mark (I don't know what your
initial document looks like). After all the occurrences have been copied
over, then you can turn it into a table.

In the code below, the Do While .Execute ... Loop construct does all the
copying, including placing a paragraph mark at the end of the inserted text.

A lot of beginners make the mistake of trying to use copy and paste,
flipping back and forth between the original document and the new one
hundreds of times. Not only is this slow, because Word has to redraw the
screen with every window change, but it's likely to fail if the macro's
timing gets thrown off.

The better course is to use VBA's "objects". One is a Document object that
represents the new document, and two more are Range objects that represent
(a) the found text in the original document and (b) the copy of it pasted
into the new document. When you have these things, then you can use the line

oDestRg.FormattedText = oSrcRg.FormattedText

to place the text in the new document without using copy/paste at all. You
don't have to flip between open windows, and it's much faster. (An
additional benefit is that it doesn't wipe out the contents, if any, of the
Windows clipboard.)

Sub RTM4()
Dim oDoc As Document
Dim oSrcRg As Range, oDestRg As Range

Set oSrcRg = ActiveDocument.Range
Set oDoc = Documents.Add
Set oDestRg = oDoc.Range
oDestRg.Collapse wdCollapseEnd

With oSrcRg.Find
.ClearFormatting
.Text = "T-?{35}"
.Forward = True
.Wrap = wdFindStop
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchAllWordForms = False
.MatchSoundsLike = False
.MatchWildcards = True

Do While .Execute
oDestRg.FormattedText = oSrcRg.FormattedText
oDestRg.Collapse wdCollapseEnd
oDestRg.InsertParagraph
oDestRg.Collapse wdCollapseEnd
Loop
End With

Set oDestRg = oDoc.Range
oDestRg.ConvertToTable Separator:=wdSeparateByTabs, _
NumColumns:=3, NumRows:=100, _
AutoFitBehavior:=wdAutoFitFixed
With oDoc.Tables(1)
.Style = "Table Grid"
.ApplyStyleHeadingRows = True
.ApplyStyleLastRow = True
.ApplyStyleFirstColumn = True
.ApplyStyleLastColumn = True
End With

oDoc.Activate

Set oSrcRg = Nothing
Set oDestRg = Nothing
Set oDoc = Nothing
End Sub

Signature

Regards,
Jay Freedman
Microsoft Word MVP FAQ: http://word.mvps.org

> Thanks Jay. This got me one step closer. Now, the macro runs. But
> it only finds, copies, and pastes one occurence of the "T-" (followed
[quoted text clipped - 69 lines]
>>> End With
>>> End Sub

Reply to this Message