Replies

Many thanks Hans – worked a treat. I just have to fine-tune the SendKeys to get then in the right directory.

However I am going to be sent a large number of workbooks every month to convert – is there any way of tying the macro to Excel instead of a particular workbook – as in normal.dot in Word? I don’t want to have to paste the macro into every workbook.

David

Although you can complete and print forms in Reader you cannot save the form+data to disc. In Reader you can only submit the field data to a server.

David

(Edited by gwhitfield on 28-Sep-02 07:02. Hyperlinks added)

I would use a userform – see here for more info:

http://www.mvps.org/word/FAQs/Userforms/CreateAUserForm.htm%5B/url%5D

The easy answer is don’t use Word. Word is a page or document processor and is no good at making publications. I’ve spent a large chunk of my life writing manuals and I’d advise you to go out and buy Ventura or Framemaker and do the job properly. It’s a lot easier in the long run.

Ventura works like a “shell” – you tell it that a manual is composed of files a, b & c and it assembles them, formats and constructs indexes etc across the whole publication. So if chapter B is present in all pubs it’s easy. And you can amend chapter b in whatever processor you fancy and Ventura will always use the latest version.

David

Yes – big sigh, so early in the morning too.

We inherited this system some years ago and only in this week have we been asked for information.

Going to sit down now and see if there is some way of pulling the specific document code info together. Looks like the start of another learning curve.

Thanks for your help Hans – spent some days near Amsterdam last year – really envy you your on the flat cycling.

David

You could try building the whole thing with a macro. We have a letter that uses a mailmerge from an Access database and writes the letter text as it goes. If you were to record a macro adding the fields you could add this in at the end of the merge.

I have attached our letter macro to this post. It’s very specialised but it does 2 merges and inserts mailmerge fields so you might be able to make use of bits of it. Very clumsy scripting I’m afraid but I’m still on the learning curve.

David

Phil,

I do this all the time for our intranet. I use a control button located in a text frame for the e-mail button and a script from Chris Rae to e-mail.

The code for the buttons:

Private Sub link_to_index_Click()

ActiveDocument.FollowHyperlink Address:= _
“aide_memoire.htm”, NewWindow:=False _
, AddHistory:=True

End Sub

Private Sub print_form_Click()

With ActiveDocument

.Shapes(1).Visible = msoFalse
.PrintOut Background:=False
.Shapes(1).Visible = msoTrue

End With

End Sub

Private Sub send_form_Click()

MailDoc3

End Sub

Button 1 returns the user to the document index, button 2 prints the form without showing the button bar and 3 e-mails the form using the script below:

Sub MailDoc3()

ActiveDocument.SaveAs FileName:=”bsg402.doc”

‘ Make instance
Set myOutlook = CreateObject(“Outlook.Application”)
‘ Make mail item
Set myMailItem = myOutlook.createitem(0)
‘ Set recipient (internal mail)

myMailItem.Recipients.Add “Stroud, Phyl”
myMailItem.Recipients.Add “Jones, Christine (SW)”

myMailItem.Recipients.Add “Humanresources”
myMailItem.Recipients.Add “SWSITESECURITY”

‘ Set subject
myMailItem.Subject = “Starters & Leavers – Team Managers”
‘ Set body
myMailItem.body = “The attached form BSG 402 contains personnel details for your attention.”
‘ Set open doc as attachement
myMailItem.Attachments.Add ActiveDocument.FullName
‘ And send it!
myMailItem.send
‘ Close instance
Set myOutlook = Nothing

End Sub

The mail macro came from Chris Rae here

http://chrisrae.com/vba/routines/sendmail.html%5B/url%5D

and the non-printing button bar from the MVP web:

http://www.mvps.org/word/FAQs/TblsFldsFms/…PrintButton.htm%5B/url%5D

David

Thanks very much for the help – printed them off and in my “Useful Tips file”.

David

I would use a userform to fill in the field and use the Close button to look at the field to see if anything has been entered. See how to build a userform here:

http://www.mvps.org/word/FAQs/Userforms/CreateAUserForm.htm

The code below is my version of the close button:

Private Sub tel_num_close_Click()

Dim validate(0)

‘ Check for entry in tel num field
validate(0) = ActiveDocument.FormFields(“tel_num”).Result

If validate(0) = “” Then

MsgBox “You must enter a number”

End If

If validate(0) “” Then

End

End If

End Sub

It’s not foolproof but it works.

David

Thanks Jefferson – worked a treat.

David

Phil,

I tried to use the mergefield technique but rapidly became confused with nested fields. In the end I discovered the catalog merge (courtesy of a Cindy Meister web article) and did a double mailmerge to produce the required doc. I have automated it with a macro.

The macro is listed below in case anybody else is interested. Half of it is recorded and other half my own scripting so I imagine it can be cleaned up a lot by someone who knows VBA. However, it’s quick and it works. I can now generate the letters off a toolbar button.

Example finished page attached.

Thanks to everybody for their tips.

David

Sub leavers_letter()

‘Open 1st doc in mailmerge catalogue mode

ChangeFileOpenDirectory “D:ManualsSYSTEMSaccess forms”
Documents.Open FileName:=”””test leavers.doc”””

‘New doc generated from merge

With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.Execute
End With

‘Header row added to table in new doc

Selection.GoTo What:=Cell, Name:=”a1″
Selection.InsertRows (1)
Selection.GoTo What:=Cell, Name:=”a1″
Selection.TypeText Text:=”System ID”
Selection.MoveRight Unit:=wdCharacter, count:=1
Selection.TypeText Text:=”Title”
Selection.MoveRight Unit:=wdCharacter, count:=1
Selection.TypeText Text:=”Copy”

Selection.GoTo What:=Cell, Name:=”a1″
Selection.SelectRow
Selection.Font.Bold = wdToggle
Selection.Rows.HeadingFormat = wdToggle

‘Cursor moved out of table to top of doc

Selection.GoTo What:=Cell, Name:=”a1″
Selection.SplitTable
Selection.TypeParagraph
Selection.TypeParagraph
Selection.MoveUp Unit:=wdLine, count:=2
Selection.TypeText Text:=”To:”
Selection.MoveDown Unit:=wdLine, count:=1

‘New doc merged again to add addressee etc fields

ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
ActiveDocument.MailMerge.OpenDataSource Name:= _
“SW-0101976NINA DAccessDataDOCUMENT.MDB”, ConfirmConversions:=False _
, ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, _
PasswordDocument:=””, PasswordTemplate:=””, WritePasswordDocument:=””, _
WritePasswordTemplate:=””, Revert:=False, Format:=wdOpenFormatAuto, _
Connection:=”QUERY Leavers Letter”, SQLStatement:= _
“SELECT * FROM [Leavers Letter]”, SQLStatement1:=””
ActiveDocument.MailMerge.EditMainDocument
ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:= _
“employees_1First_Name”
Selection.TypeText Text:=” ”
ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:= _
“employees_1Last_Name”
Selection.TypeParagraph
ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:= _
“Dept_Name”
Selection.TypeParagraph
ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:= _
“Location_3Location”
Selection.TypeParagraph
Selection.TypeParagraph
ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:= _
“employeesFirst_Name”
Selection.TypeText Text:=” ”
ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:= _
“employeesLast_Name”
Selection.TypeParagraph

‘Letter text added

Selection.TypeText Text:= _
“Document Control have been informed that the above employee ”
Selection.TypeText Text:= _
“has left the company. The documents issued to the above are ”
Selection.TypeText Text:=”listed below.”
Selection.TypeParagraph
Selection.TypeParagraph
Selection.TypeText Text:= _
“Document Control would appreciate the return of these docume”
Selection.TypeText Text:=”nts.”

‘ Mailmerged to form complete letter

With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.Execute
End With

‘Merge will produce as many letters as there are records in the query.
‘Cursor set to start of page 2 and remaing doc selected

‘count pages
Dim intpage As Integer

ActiveDocument.Repaginate
intpage = ActiveDocument.BuiltInDocumentProperties(wdPropertyPages)
‘MsgBox ActiveDocument.BuiltInDocumentProperties(wdPropertyPages)

‘if more than 1 page go to page 2
If intpage > 1 Then

Selection.GoTo What:=wdGoToPage, Which:=wdGoToFirst, count:=2, Name:=””
Selection.Find.ClearFormatting
With Selection.Find
.Text = “page”
.Replacement.Text = ” ”
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With

‘if only 1 page exit sub
Else
Exit Sub

End If

‘Selected text deleted

Selection.EndKey Unit:=wdStory, Extend:=wdExtend
Selection.delete Unit:=wdCharacter, count:=1
ActiveWindow.ActivePane.SmallScroll Down:=-24
Selection.TypeBackspace

‘Backspace to delete new page section break
‘single page letter remaining

Selection.delete Unit:=wdCharacter, count:=1

End Sub

Ann,

Fixed it, although it seems a bit of a fudge.

I discovered a DATABASE toolbar lurking at the top of the screen and used that instead. I abandoned mailmerge and inserted the data as fields. It meant I had to have 3 queries that each held just the information I required (addressee, employee concerned & list of manuals) but each query only has to have a number entered in it so it’s not too onerous.

But it works!

Thank you for your help,

David

This switches the protection off:

ActiveDocument.Unprotect

This switxches it on:

ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True

David

I’ve this – rarely I’m glad to say! – on various docs.

There seems to be some error in the doc that causes it. I’ve got round it by:

a) Set the cursor at the very beginning of the doc and try a save;

or

Select all your text, copy to the clipboard and paste into a new doc;

or

c) Copy all the text to the clipboard, paste into a plain text editor and save as a text (.txt) file. That strips out everything that Word puts in. Open text file in Word and reformat.

David

I had a document (the source) containing the includetext fields that linked to three docs – the first two docs had one template and the last a different. All the docs were in the same folder.

The docs printed but all used the header & footers of the first doc and the last doc – with the different template – was all over the place.

David