Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 739
  • Last Modified:

how to stop do loop macro when it reaches the last page of a word document

After doing a mail merge in word 97 one of the fields in the merged document contains numbers which are a shortcut for autotext entries ie.0001 is the short cut for one name and address, 0002 is the shortcut for another name and address and so on.
i use a "do loop" macro to update all the number fields in the merged document. However i do not know how to make the macro stop when it reaches the last autotext entry ie 0009. Instead the macro just hangs.Because i use various autotext numbers ie 0189 or 0847 i would like the loop macro to just end on the last page of the merged document irrespective of which  autotext number has been entered.
The macro code is as follows:

   Do
   Selection.MoveDown Unit:=wdLine, Count:=2
   Selection.MoveRight Unit:=wdCharacter, Count:=1
   Selection.Range.InsertAutoText
   Application.Browser.Next
   Loop

Thanking you in anticipation to anyone who can help
0
anonymouse
Asked:
anonymouse
  • 3
  • 3
1 Solution
 
bruintjeCommented:
Hello anonymouse,

you know which autotext entry is last in this structure or make one called 9999 and when you encounter that one jump out of the loop like

  Do
     Selection.MoveDown Unit:=wdLine, Count:=2
     Selection.MoveRight Unit:=wdCharacter, Count:=1
     Selection.Range.InsertAutoText
     Application.Browser.Next
     if autotext = 9999 then Exit Do
   Loop

or something like that

HAGD:O)Bruintje
0
 
anonymouseAuthor Commented:
Thank you for your quick response Bruintje
i would like to add the following information to clarify the problem
The autotext entries vary every day ie one day they may be 0001, 0347,0865 - the next day they may be 0245,1134,1283 - Therefore i suppose i could edit the macro and insert the last autotext number but it would be much nicer if the do loop macro would end irrespective of whichever number was the last autotext entry.
0
 
bruintjeCommented:
where do these entries come from? i mean is it read from a list which to take on which day
0
Independent Software Vendors: 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!

 
anonymouseAuthor Commented:
the entries come from a spreadsheet which looks like this

HEADINGS = date /doctors code /name /address   /postcode
DATA     =1.2.03/ 0234        /fred / 1 acacia / N23 7HY

Each day we add new patients to the spreadsheet and we use a different doctors code - because all the patients cannot be added to the same doctors list of patients.

We then do the mailmerge with a letter in word
The doctors code number ie. 0234 is an autotext which displays the doctors name and address
There may be 20 letters or more each day with different code numbers
   

0
 
bruintjeCommented:
aha so you know when you're at the end of that list and then you know which is the last used number[doctor] record

have you automated the merge? i mean can open the excel sheet and look up the last used number there like

Sub y()
Dim xlObj As Excel.Application
Dim LastNum As String

  Set xlObj = CreateObject("Excel.Application")
  xlObj.Workbooks.Open ("Mymergedocumentpath+docname here")
  'write a small routine to look up the last used doctor
  LastNum = "the answer from above"
  xlObj.Workbooks(1).Close False
  Set xlObj = Nothing
  Do
  'do your thing here
  If autotextenry = LastNum Then Exit Do
  Loop
End Sub

or that's the idea
0
 
anonymouseAuthor Commented:
Sorry bruintje i did not reply earlier but the cleaner threw me out of the office and i dont have internet access at home.
Thank you for your reply - now i have more time i can explain a little bit better what we do.

first we go to excel and enter a daily list of patients details that need to be allocated to different doctors and save the data (see above for the different headings under which we enter information - especially the doctors code number)
Secondly,we open the word document and run a mailmerge with the data in excel *** using -query options-field- date- compare to eg.1/4/03 or whichever is the current date ***
After the merge we update all the doctors code numbers with their respective autotext entries(before we use to click each page and press F3 to update the doctor autotext entry) now we can use the do loop to update all the doctor autotext entries in all the letters.
The problem is that on the last letter the do loop just hangs with a message of:
Run-Time error '5906'
The range does not specify a known AutoText entry
Then you have the following option - End - Debug - Help
Finally we print all the letters
If you can solve this problem then we can create a Macro that can:- mailmerge, update autotext entries, and print the letters in one go.
But then i need to be able to edit the macro to stop it asking if i want to save the letters.This i dont know how to do either.

Thank you for all your help -  i do appreciate it !!!


 
0
 
moduloCommented:
PAQed - no points refunded (of 50)

modulo
Community Support Moderator
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now