I would probably not use mailmerge to do that. However, using your
mailmerge main document with the data source attached to it, you could
modify the code in the following macro so that it obtained the invoice
number from your text file and used it to set the value of a document
variable to be displayed in a { DOCVARIABLE } field that you insert
(manually) into the mail merge main document:
Sub MailMergeformExcelwithFormFields()
Dim dSource As String
Dim qryStr As String
Dim mfCode As Range
Dim i As Long, j As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset
With ActiveDocument
'Get the details of the datasource
With .MailMerge.DataSource
dSource = .Name
qryStr = .QueryString
End With
'Convert the MERGEFIELDS to DOCVARIABLE fields
For i = 1 To .Fields.Count
If .Fields(i).Type = wdFieldMergeField Then
Set mfCode = .Fields(i).code
mfCode = Replace(mfCode, "MERGEFIELD", "DOCVARIABLE")
End If
Next i
'Convert the Mail Merge Main document to a normal Word document
.MailMerge.MainDocumentType = wdNotAMergeDocument
.Protect wdAllowOnlyFormFields, NoReset
End With
' Open the database
Set db = OpenDatabase(dSource, False, False, "Excel 8.0")
' Retrieve the recordset
Set rs = db.OpenRecordset(qryStr)
With rs
' Move to the first record
.MoveFirst
j = 1
Do While Not .EOF
'Create variables in the document with the names and values of the
fields in each record
For i = 0 To .Fields.Count - 1
If .Fields(i).Value "" Then
ActiveDocument.Variables(.Fields(i).Name).Value =
..Fields(i).Value
End If
Next i
With ActiveDocument
.Fields.Update
.SaveAs "C:\Test\MwithFF" & j
End With
.MoveNext
j = j + 1
Loop
End With
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End Sub
--
Hope this helps.
Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.
Doug Robbins - Word MVP, originally posted via msnews.microsoft.com
"Nick_F" wrote in message
...
Oops, sorry, GIGO problem, my fault.
I forgot to mention I am starting with data pulled from a fox pro client
database using crystal reports and bespokely-dumped into an Excel data
source
file.
I can make every part of this work except the need to intercept the next
available bill number from the text file on my office network file server,
increment that by one for each bill generated by the merge and then dump
the
last bill's number back into the text file on the network, or dump the
original number back if the merge doesn't complete successfully or I abort
it...
(Incidentally I found the Doug Robbins introduction to Userforms page most
helpful on a previous occasion / project!)
Nick
"Doug Robbins - Word MVP" wrote:
Unless you already have the information that will be the data source for
the
merge already in some application, you would be better off using a
userform
into which you enter that data.
See the article "How to create a Userform" at:
http://word.mvps.org/FAQs/Userforms/CreateAUserForm.htm
and the following pages of fellow MVP Greg Maxey's website :
http://gregmaxey.mvps.org/Create_and...a_UserForm.htm
http://gregmaxey.mvps.org/Populate_UserForm_ListBox.htm
and also
the last in the following series of articles:
http://www.mousetrax.com/techpage.html#autoforms
Please Fill Out This Form
Part 1: Create professional looking forms in Word
http://www.computorcompanion.com/LPMArticle.asp?ID=22
Part 2: Adding Automation to your Word forms.
http://www.computorcompanion.com/LPMArticle.asp?ID=46
Part 3: Learn more VBA (macros) to automate your forms.
http://www.computorcompanion.com/LPMArticle.asp?ID=119
Part 4: Use custom dialog boxes in your Word forms
http://www.computorcompanion.com/LPMArticle.asp?ID=127
Part 5: Connect your AutoForm to a database to save input time and keep
better records!
http://www.computorcompanion.com/LPMArticle.asp?ID=136
--
Hope this helps.
Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.
Doug Robbins - Word MVP, originally posted via msnews.microsoft.com
"Nick_F" wrote in message
...
I use an autonew macro at present to find a text file, collect a number,
launch a new bill document from a template and print the "next" bill
number
on the bill - the number in the text file then increments by one.
I am experimenting with generating a mail-merge fillable bill template
and
want to be able to pick up a new number for each bill in a bill run so
each
printed copy gets a unique number. My other users will, however, need
to
be
able to go on accessing the autonew macro in the non-mergeable template
to
deal with individual bills. I may also make mistakes in running the
mail
merge and want to be able to have an option to reset the starting
number
back
to whatever it began at if I abandon the mail merge. Any (really simple
as
I
am a techno-clot) solutions spring to mind?