macro works sometimes

[Margaret Upton]

Some time ago I recorded a macro that would copy and paste text between 2
open documents, finding the text by bookmarks. There had to be an
alteration to the macro and it would not let me alter it, so I recorded it
again, in exactly the same way as the first macros that worked. I am not a
programmer and would not be able to actually write the code. When I test
the macro the first time it works, sometimes twice, then when I put it on
the machine in the office it works maybe twice then comes up with a variety
of error message, one of them being cannot find endnote. There is not an
endnote in either of the documents and it will work perfectly well a couple
of times. I have recorded and re-recorded it until I am at my wits end to
know what else to do. Why does it stop working when it works the original
time you test it. I have read the section in my Office 2000 book, which
clearly states that you can record macros. Could anyone help please.

Margaret

 

[Dave Lett]

Hi Margaret

Yes, someone here can probably help. However, it will most likely require that you abandon the recorded marco. In a list, can you tell us what you want (what you start with and what you want to end up with)? Are both documents open? Do their file names change?

I assume that you want to
1) identify text or graphics in DocA, Bookmark
2) copy that text to DocB, Bookmark
3) close? save?
4) repeat the procedure a number of times (is so, what is repeating and what is not; that is, have the names of the bookmarks or the names of either document changed?)

 

[Margaret Upton]

Hi Dave

I get the first macro to open a template called memo which contains
bookmarks with merge codes. In this macro the user simply fills in the
form, using F11 to go from place to place between the bookmarks and saves
the file. Then 4 macros follow, each macro opens a template (with the memo
still open) and then switches backward and forward copying between the
bookmarks in the memo and going to words or + signs and then pasting in the
information from the memo to the letter. It also expands some autotext at
the bottom of the document. I have put an example of the mem macro and one
of the letter macros below. This has worked well in the past and now it has
started throwing up error messages such as "entered multiple destination
from a footnote, endnote or comment". I would be so grateful for some help
because I have recoreded it and rerecorded it so many times I am tearing my
hair out. Thanks so much.

Margaret

This is the first mem macro

Sub mem()

'

' mem Macro

' Macro recorded 3/02/04 by Margaret Upton

'





Documents.add Template:= _

"C:\WINDOWS\Application Data\Microsoft\Templates\Letters\memo.dot",
_

NewTemplate:=False, DocumentType:=0

Selection.NextField.Select

Application.Run MacroName:="Textoff"

With Dialogs(wdDialogFileSaveAs)

.Name = "C:\My Documents\Memos\"

.Show

End With





End Sub


THIS IS THE OTHER MACRO

' vs Macro

' Macro recorded 3/02/04 by Margaret Upton

'

Documents.add Template:= _

"C:\WINDOWS\Application Data\Microsoft\Templates\Letters\Vendors
sols letter.dot" _

, NewTemplate:=False, DocumentType:=0

Windows(2).Activate

Selection.HomeKey Unit:=wdStory

Selection.EndKey Unit:=wdLine, Extend:=wdExtend

Selection.MoveLeft Unit:=wdCharacter, Count:=1, Extend:=wdExtend

Selection.copy

Windows(1).Activate

Selection.EndKey Unit:=wdLine

Selection.paste

Windows(2).Activate

Selection.GoTo What:=wdGoToBookmark, Name:="vens"

Selection.Find.ClearFormatting

With Selection.Find

.Text = "Re:"

.Replacement.Text = ""

.Forward = True

.Wrap = wdFindAsk

.Format = False

.MatchCase = False

.MatchWholeWord = False

.MatchWildcards = False

.MatchSoundsLike = False

.MatchAllWordForms = False

End With

Selection.Extend

Selection.GoTo What:=wdGoToBookmark, Name:="vens2"

Selection.Find.ClearFormatting

With Selection.Find

.Text = "Re:"

.Replacement.Text = ""

.Forward = True

.Wrap = wdFindContinue

.Format = False

.MatchCase = False

.MatchWholeWord = False

.MatchWildcards = False

.MatchSoundsLike = False

.MatchAllWordForms = False

End With

Selection.copy

Windows(1).Activate

Selection.HomeKey Unit:=wdStory

Selection.Find.ClearFormatting

With Selection.Find

.Text = "for the attention of"

.Replacement.Text = ""

.Forward = True

.Wrap = wdFindContinue

.Format = False

.MatchCase = False

.MatchWholeWord = False

.MatchWildcards = False

.MatchSoundsLike = False

.MatchAllWordForms = False

End With

Selection.Find.Execute

Selection.EndKey Unit:=wdLine

Application.Run MacroName:="Normal.NewMacros.pastesimple"

Windows(2).Activate

Selection.GoTo What:=wdGoToBookmark, Name:="add"

Selection.Find.ClearFormatting

With Selection.Find

.Text = "for the attention of"

.Replacement.Text = ""

.Forward = True

.Wrap = wdFindAsk

.Format = False

.MatchCase = False

.MatchWholeWord = False

.MatchWildcards = False

.MatchSoundsLike = False

.MatchAllWordForms = False

End With

Selection.Extend

Selection.GoTo What:=wdGoToBookmark, Name:="add2"

Selection.Find.ClearFormatting

With Selection.Find

.Text = "for the attention of"

.Replacement.Text = ""

.Forward = True

.Wrap = wdFindContinue

.Format = False

.MatchCase = False

.MatchWholeWord = False

.MatchWildcards = False

.MatchSoundsLike = False

.MatchAllWordForms = False

End With

Selection.copy

Windows(1).Activate

Selection.HomeKey Unit:=wdStory

Selection.Find.ClearFormatting

With Selection.Find

.Text = "Re:"

.Replacement.Text = ""

.Forward = True

.Wrap = wdFindContinue

.Format = False

.MatchCase = False

.MatchWholeWord = False

.MatchWildcards = False

.MatchSoundsLike = False

.MatchAllWordForms = False

End With

Selection.Find.Execute

Selection.EndKey Unit:=wdLine

Application.Run MacroName:="Normal.NewMacros.pastesimple"

Windows(2).Activate

Selection.GoTo What:=wdGoToBookmark, Name:="ven"

Selection.Find.ClearFormatting

With Selection.Find

.Text = "Re:"

.Replacement.Text = ""

.Forward = True

.Wrap = wdFindAsk

.Format = False

.MatchCase = False

.MatchWholeWord = False

.MatchWildcards = False

.MatchSoundsLike = False

.MatchAllWordForms = False

End With

Selection.Extend

Selection.GoTo What:=wdGoToBookmark, Name:="ven2"

Selection.Find.ClearFormatting

With Selection.Find

.Text = "Re:"

.Replacement.Text = ""

.Forward = True

.Wrap = wdFindContinue

.Format = False

.MatchCase = False

.MatchWholeWord = False

.MatchWildcards = False

.MatchSoundsLike = False

.MatchAllWordForms = False

End With

Selection.copy

Windows(1).Activate

Selection.HomeKey Unit:=wdStory

Selection.Find.ClearFormatting

With Selection.Find

.Text = "+"

.Replacement.Text = ""

.Forward = True

.Wrap = wdFindContinue

.Format = False

.MatchCase = False

.MatchWholeWord = False

.MatchWildcards = False

.MatchSoundsLike = False

.MatchAllWordForms = False

End With

Selection.Find.Execute

Selection.Delete Unit:=wdCharacter, Count:=1

Application.Run MacroName:="Normal.NewMacros.pastesimple"

Windows(2).Activate

Selection.GoTo What:=wdGoToBookmark, Name:="pur"

Selection.Find.ClearFormatting

With Selection.Find

.Text = "+"

.Replacement.Text = ""

.Forward = True

.Wrap = wdFindAsk

.Format = False

.MatchCase = False

.MatchWholeWord = False

.MatchWildcards = False

.MatchSoundsLike = False

.MatchAllWordForms = False

End With

Selection.Extend

Selection.GoTo What:=wdGoToBookmark, Name:="pur2"

Selection.Find.ClearFormatting

With Selection.Find

.Text = "+"

.Replacement.Text = ""

.Forward = True

.Wrap = wdFindContinue

.Format = False

.MatchCase = False

.MatchWholeWord = False

.MatchWildcards = False

.MatchSoundsLike = False

.MatchAllWordForms = False

End With

Selection.copy

Windows(1).Activate

Selection.HomeKey Unit:=wdStory

Selection.Find.ClearFormatting

With Selection.Find

.Text = "++"

.Replacement.Text = ""

.Forward = True

.Wrap = wdFindContinue

.Format = False

.MatchCase = False

.MatchWholeWord = False

.MatchWildcards = False

.MatchSoundsLike = False

.MatchAllWordForms = False

End With

Selection.Find.Execute

Selection.Delete Unit:=wdCharacter, Count:=1

Application.Run MacroName:="Normal.NewMacros.pastesimple"

Windows(2).Activate

Selection.HomeKey Unit:=wdStory

Selection.Find.ClearFormatting

With Selection.Find

.Text = "/"

.Replacement.Text = ""

.Forward = True

.Wrap = wdFindContinue

.Format = False

.MatchCase = False

.MatchWholeWord = False

.MatchWildcards = False

.MatchSoundsLike = False

.MatchAllWordForms = False

End With

Selection.Find.Execute

Selection.MoveRight Unit:=wdCharacter, Count:=1

Selection.MoveRight Unit:=wdCharacter, Count:=2, Extend:=wdExtend

Selection.copy

Windows(1).Activate

Selection.HomeKey Unit:=wdStory

Selection.Find.ClearFormatting

With Selection.Find

.Text = "miller evans"

.Replacement.Text = ""

.Forward = True

.Wrap = wdFindContinue

.Format = False

.MatchCase = False

.MatchWholeWord = False

.MatchWildcards = False

.MatchSoundsLike = False

.MatchAllWordForms = False

End With

Selection.Find.Execute

Selection.MoveUp Unit:=wdLine, Count:=1

Selection.paste

Selection.Range.InsertAutoText

Selection.HomeKey Unit:=wdStory

Selection.NextField.Select

Selection.NextField.Select

End Sub

Hi Margaret,

Yes, someone here can probably help. However, it will most likely require

that you abandon the recorded marco. In a list, can you tell us what you
want (what you start with and what you want to end up with)? Are both
documents open? Do their file names change?

I assume that you want to
1) identify text or graphics in DocA, BookmarkA
2) copy that text to DocB, BookmarkB
3) close? save?
4) repeat the procedure a number of times (is so, what is repeating and

what is not; that is, have the names of the bookmarks or the names of either
document changed?)

 

[Dave Lett]

Hi Margaret

I printed the routines you sent; 8 pages is a LOT to review. I see some things that you can do to start improving the usability of your code. IMHO, you will have to abandon recording this macro (based on your testimony that it repeated fails and based on the weaknesses in the code that you cannot avoid by recording). IMHO, you'll have to write this routine

I found nothing really preplexing or difficult your routine "Sub mem()"

"Sub vs()", however, is another story. Let's start from the beginning: you're going to create and set some variables

Public Sub vs(
Dim oDocLetter As Documen
Dim oDocMemo As Documen
oDocLetter = Documents.Add
(Template:="Insert your template",
NewTEmplate:=False,
DocumentType:=0

''' this next line can cause a lot of problems, let'
''' get rid of it and replace it with something like th
''' line after i
Windows(2).Activat
oDocMemo = Documents("Can you enter the name of the other document?"

Before we move on, let's get this much finished. What are the two documents that we're dealing with

HTH
Dave

 

[Margaret Upton]

Dave

So kind of you to take this trouble. I rerecorded it again this morning and
it worked twice, then stopped working. If I can tell you basically what the
VS macro does. There are 4 macros, pur, ps, ven & vs. They are 4 standard
letters. The VS macro is to the vendors solicitors, the vs & ps macros are
very much the same and the ven and pur letters don't have as much
information to copy over but need a price putting in.

So the vs macro basically step by step first of all goes to the top of the
memo and just copies the reference, goes back to the other template and just
pastes it into the top of the page, it then goes back to the memo and copies
between the book marks vens and vens, then goes back and looks for "for the
attention of" and pastes it onto the end, goes back to the memo and copies
between bookmarks add and add2, switches back, searches for Re: and pastes
that in, switches back to the memo and copies between bookmarks ven and ven2
, switches back and searches for + then pastes it in, goes back to the memo
copies between bookmarks pur and pur2, goes back to the second document,
searches for ++ and pastes it in there, goes back to the memo and searches
for / moves forward 2 spaces, which then gets the middle of the reference
(eg ref T1234/DE/MU) so that it copies the de, goes back to the letter
searches for Miller Evans, goes up a line pastes in the DE (or whichever one
it is) and expands the auto text. So if you cannot make head nor tail of
that it copies the reference, the solicitors name, the name of the property,
the vendors name, the purchasers name, the initials from the reference and
that expands that in autotext to give the name of the partner.

To answer your question they are 2 templates, the first one is called memo
and the second one is called Vendors sols letter.

What I simply cannot understand, is that 12 months ago I recorded all this
and it has worked perfectly well. They have added a letter in front of the
reference and it would not let me alter it, and when I re recorded them now
they only work a couple of times. I simply cannot understand what has
happened.

I am so grateful for your help.

Margaret

Hi Margaret,

I printed the routines you sent; 8 pages is a LOT to review. I see some

things that you can do to start improving the usability of your code. IMHO,
you will have to abandon recording this macro (based on your testimony that
it repeated fails and based on the weaknesses in the code that you cannot
avoid by recording). IMHO, you'll have to write this routine.

I found nothing really preplexing or difficult your routine "Sub mem()".

"Sub vs()", however, is another story. Let's start from the beginning:

you're going to create and set some variables:

Public Sub vs()
Dim oDocLetter As Document
Dim oDocMemo As Document
oDocLetter = Documents.Add _
(Template:="Insert your template", _
NewTEmplate:=False, _
DocumentType:=0)

''' this next line can cause a lot of problems, let's
''' get rid of it and replace it with something like the
''' line after it
Windows(2).Activate
oDocMemo = Documents("Can you enter the name of the other document?")


Before we move on, let's get this much finished. What are the two

documents that we're dealing with?

 

[Dave Lett]

Hi Margaret

I've tried responding to this post a couple of time now and still have seen the response posted, but I'll try again. Thanks for the explanation of the routines; I gathered that's what you wanted to do. BTW and FYI, you don't have two templates; rather, you have two documents _based_ on two templates (memo.dot and Vendors sols letter.dot). Do you know the names of the documents based on the two templates

Dave

 

[Margaret Upton]

Hi Dave

The 2 documents are not named until they are finished, that is why I used
the ActiveDocument. I tried when I recorded it the first time just
switching documents, but then it could not find the document, so I switched
documents from the Windows on the button bar, which then was ActiveDocument
1 and 2. I don't know what you mean by BTW and FYI ? Sorry.

Thanks for your help.

Margaret

Hi Margaret,

I've tried responding to this post a couple of time now and still have

seen the response posted, but I'll try again. Thanks for the explanation of
the routines; I gathered that's what you wanted to do. BTW and FYI, you
don't have two templates; rather, you have two documents _based_ on two
templates (memo.dot and Vendors sols letter.dot). Do you know the names of
the documents based on the two templates?

 

[Dave Lett]

Hi Margaret

BTW = by the wa
FYI = For your informatio

Look at the original code you posted. Read the first two blocks that start with "With Selection.Find". The first one doesn't have an .Execute statement, which means that, after going to the bookmark "vens" it never searches for "Re:". Is that a mistake

Dave

 

[Margaret Upton]

Hi Dave

Yes, its a mistake, I had recorded them so many times, I would just have
pressed the esc key to get rid of it, I should have realised it would show
up like that, sorry to confuse you. I manage to get macros to work by
recording and putting in bits of code that I have seen or been given, hense
the being able to save to a directory etc without recording that part. I
actually manage to automate quite a few things, more by luck than judgement
I think !!!

Thanks so much.

Margaret

Hi Margaret,

BTW = by the way
FYI = For your information

Look at the original code you posted. Read the first two blocks that start

with "With Selection.Find". The first one doesn't have an .Execute
statement, which means that, after going to the bookmark "vens" it never
searches for "Re:". Is that a mistake?

 

[Dave Lett]

Hi Margaret

Here's some code that does a lot of what you're looking for

Dim oRngToCopy As Rang
Dim oDoc As Documen
Dim oDocLetter As Documen
Dim oDocMemo As Documen
Set oDocLetter = Documents.Add
(Template:="Normal.dot",
NewTEmplate:=False,
DocumentType:=0

''' this is only to set a document object tha
''' can access the methods and properties of th
''' document based on the memo templat
For Each oDoc In Document
If LCase(oDoc.AttachedTemplate) = "memo.dot" The
Set oDocMemo = oDo
End I
Next oDo

''' activate the document based on the memo templat
''' and copy the first lin
oDocMemo.Activat
With Selectio
.HomeKey Unit:=wdStor
.EndKey Unit:=wdLine, Extend:=wdExten
.Cop
End Wit

''' activate the letter document and paste the copie
''' first line from the memo documen
oDocLetter.Activat
With Selectio
.EndKey Unit:=wdLin
.Past

''' set a range object to equal all the material betwee
''' vens and vens
With oDocMem
Set oRngToCopy = .Range
(Start:=.Bookmarks("vens").Start,
End:=.Bookmarks("vens2").End
End Wit

''' in letter document, find "for the attention of
With .Fin
.ClearFormattin
.Text = "for the attention of
.Execut
End Wit
.EndKey Unit:=wdLin
''' replaces pastesimpl
''' at the end of the line for "for the attention of
''' paste the material between the bookmarks vens an
''' vens2 from the memo documen
.Range.FormattedText = oRngToCopy.FormattedTex

''' set a range object to equal all the material betwee
''' add and add
With oDocMem
Set oRngToCopy = .Range
(Start:=.Bookmarks("add").Start,
End:=.Bookmarks("add2").End
End Wit
''' once you find what you're looking for
''' you don't need to repeat all the line
''' of code to find it agai
.Find.Execut
''' the next line is Selection.Cop
''' is this right? if so, you're copying "for the attention of"
''' activating the letter document, finding "Re:", pasting "for the attention of
''' at the end of the line that contains "Re:
End Wit

Notice how the code doesn't force you to switch back and forth between the two documents so often. I stopped here because I got a little confused about your intentions and whether you might have recorded something you didn't intend. However, I think the sample code shows you everything you might need to write the rest of the routine. That is, each time you want to copy the material between two bookmarks, set the oRngToCopy so that it identifies the two bookmarks (you don't need to activate the document because you set the document object). Then, you can use the .FormattedText property instead of Selection.Copy and Selection.Paste (if you don't want to copy formatting or pictures, you can use .Text instead of .FormattedText)

HTH
Dave

 

[Margaret Upton]

Dave

Thank you so very much, I am very very grateful, you are a star.

Margaret

Hi Margaret,

Here's some code that does a lot of what you're looking for:

Dim oRngToCopy As Range
Dim oDoc As Document
Dim oDocLetter As Document
Dim oDocMemo As Document
Set oDocLetter = Documents.Add _
(Template:="Normal.dot", _
NewTEmplate:=False, _
DocumentType:=0)

''' this is only to set a document object that
''' can access the methods and properties of the
''' document based on the memo template
For Each oDoc In Documents
If LCase(oDoc.AttachedTemplate) = "memo.dot" Then
Set oDocMemo = oDoc
End If
Next oDoc

''' activate the document based on the memo template
''' and copy the first line
oDocMemo.Activate
With Selection
.HomeKey Unit:=wdStory
.EndKey Unit:=wdLine, Extend:=wdExtend
.Copy
End With

''' activate the letter document and paste the copied
''' first line from the memo document
oDocLetter.Activate
With Selection
.EndKey Unit:=wdLine
.Paste

''' set a range object to equal all the material between
''' vens and vens2
With oDocMemo
Set oRngToCopy = .Range _
(Start:=.Bookmarks("vens").Start, _
End:=.Bookmarks("vens2").End)
End With

''' in letter document, find "for the attention of"
With .Find
.ClearFormatting
.Text = "for the attention of"
.Execute
End With
.EndKey Unit:=wdLine
''' replaces pastesimple
''' at the end of the line for "for the attention of"
''' paste the material between the bookmarks vens and
''' vens2 from the memo document
.Range.FormattedText = oRngToCopy.FormattedText

''' set a range object to equal all the material between
''' add and add2
With oDocMemo
Set oRngToCopy = .Range _
(Start:=.Bookmarks("add").Start, _
End:=.Bookmarks("add2").End)
End With
''' once you find what you're looking for,
''' you don't need to repeat all the lines
''' of code to find it again
.Find.Execute
''' the next line is Selection.Copy
''' is this right? if so, you're copying "for the attention of",
''' activating the letter document, finding "Re:", pasting "for the attention of"
''' at the end of the line that contains "Re:"
End With


Notice how the code doesn't force you to switch back and forth between the

two documents so often. I stopped here because I got a little confused about
your intentions and whether you might have recorded something you didn't
intend. However, I think the sample code shows you everything you might need
to write the rest of the routine. That is, each time you want to copy the
material between two bookmarks, set the oRngToCopy so that it identifies the
two bookmarks (you don't need to activate the document because you set the
document object). Then, you can use the .FormattedText property instead of
Selection.Copy and Selection.Paste (if you don't want to copy formatting or
pictures, you can use .Text instead of .FormattedText).