?
Solved

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

Posted on 2003-03-28
8
Medium Priority
?
736 Views
Last Modified: 2008-03-17
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
Comment
Question by:anonymouse
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
8 Comments
 
LVL 44

Expert Comment

by:bruintje
ID: 8226483
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
 

Author Comment

by:anonymouse
ID: 8226872
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
 
LVL 44

Expert Comment

by:bruintje
ID: 8226906
where do these entries come from? i mean is it read from a list which to take on which day
0
Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 

Author Comment

by:anonymouse
ID: 8227165
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
 
LVL 44

Expert Comment

by:bruintje
ID: 8227567
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
 

Author Comment

by:anonymouse
ID: 8247445
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
 

Accepted Solution

by:
modulo earned 0 total points
ID: 10331269
PAQed - no points refunded (of 50)

modulo
Community Support Moderator
0

Featured Post

Technology Partners: 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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
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…

770 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