saving a doc with a filename from a merged field

[spanker]

hi all relatively new to all this so hope i can get some help or pionters

i have a word template that i use,i merge the data from an excel sheet.
what i woulkd like to do is
when i merge a new document from a record in the excel sheet.
in the save dialog box i need it to put in the data from one of the merged
fields
(ie james smith) with a unique ref number and save to a spacific folder

ive found some vba code that will give me a unique ref number everytime the
template is used which works great.

i just dont know how to get the merged data to go in the save dialog box

this is the code i use for the unique ref number (thanks to Doug robbins)

Sub AutoNew()

Order = System.PrivateProfileString("C:\Settings.Txt", _
"MacroSettings", "Order")

If Order = "" Then
Order = 1
Else
Order = Order + 1
End If

System.PrivateProfileString("C:\Settings.txt", "MacroSettings", _
"Order") = Order

ActiveDocument.Bookmarks("Order").Range.InsertBefore Format(Order, "00#")
ActiveDocument.SaveAs FileName:="path" & Format(Order, "00#")

End Sub

thanks

Kevin

 

[Graham Mayor]

If this is a mail merge - see
http://www.gmayor.com/individual_merge_letters.htm otherwise I am not sure
how you are 'merging' the data from Excel for a single document that could
use your macro. You need to define the 'specific folder' and the merged
information that you want to be included should be bookmarked (here as
'Field'). Once the merge has taken place the merge field no longer exists
and has been replaced with the merged data. How you bookmark that rather
depends on your process and perhaps its position in the document (if
consistently in the same place).

You don't actually have to insert the number into the document for it to be
included in the filename, but if you are going to do so then it is better to
insert it into the 'Order' bookmark rather than next to it - especially if
you might run the macro again on the same document. If you run the macro as
an autonew macro, then it seems likely that at this stage the merged data
will not have been included, so you may have to complete the task after the
data is merged.

Dim sPath As String
Dim sName As String
Dim rNum As Range
sPath = "D:\My Documents\Test\"
Order = System.PrivateProfileString("C:\Settings.Txt", _
"MacroSettings", "Order")
If Order = "" Then
Order = 1
Else
Order = Order + 1
End If
System.PrivateProfileString("C:\Settings.txt", _
"MacroSettings", _
"Order") = Order
With ActiveDocument
Set rNum = ActiveDocument.Bookmarks("Order").Range
rNum = Format(Order, "00#")
.Bookmarks.Add "Order", rNum
sName = .Bookmarks("Field").Range.Text
.SaveAs FileName:=sPath & sName & "_" & _
Format(Order, "00#")
End With


--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[spanker]

thanks for the quick reply Graham

sorry if i didnt explain it very well
the form i use is a word template (.dot) file,when i doublle click this it
opens a new document (.doc) file based on the original template.

i then use mail merge to merge to new document entering the appropriate
record number from my excel sheet which then gives me my form with all the
required merge data from my excel sheet (name, address, tel, ect).

i then save this file with a filename
what im trying to do is when i goto save the file it already has the
filename and unique number in the save as dialog box. ie Save as : John Smith
012345.
at present it saves as pathunique number which i have to manually edit

john smith being the data merged from the excel record
the merge fields are consistantly in the same place (address block)
i tried to bookmark the disired merge fields in the original .dot template
but it didnt work.

in the original code i posted could i change the original "path" to the data
from the merged record
thanks

kevin

 

[Graham Mayor]

This is not quite as straightforward as you imagine as following the merge,
the fields no longer exist - merely the results. However, if you have the
name field that you want to use as part of the filename bookmarked as 'Name'
(insert the address as separate fields as you will not be able to extract
part of an address block field in this way) in your template, and the
incrementing number bookmarked as 'Order'. The following macro in your
template will prompt for the number of the record you want to merge. It
opens that record, unlinks the fields to fix that record then saves with the
name and number as filename. As it does not merge to a new document, but
modifies the document created from the template, the bookmarks are not lost.
This will only work for single records, and you must know the record number
in advance.

Sub AutoNew()
Dim iRec As Integer
Dim oDoc As Document
Dim fName As String
Dim sPath As String
Dim rNum As Range
sPath = "D:\My Documents\Test\"
Order = System.PrivateProfileString("C:\Settings.Txt", _
"MacroSettings", "Order")
If Order = "" Then
Order = 1
Else
Order = Order + 1
End If
System.PrivateProfileString("C:\Settings.txt", _
"MacroSettings", _
"Order") = Order
iRec = InputBox("Merge which record number?")
ActiveWindow.View.ShowFieldCodes = False
Set oDoc = ActiveDocument
With oDoc
Set rNum = .Bookmarks("Order").Range
rNum = Format(Order, "00#")
.Bookmarks.Add "Order", rNum
With .MailMerge
.ViewMailMergeFieldCodes = False
.DataSource.ActiveRecord = iRec
fName = oDoc.Bookmarks("Name").Range.Fields(1).Result
.MainDocumentType = wdNotAMergeDocument
End With
For i = .Fields.Count To 1 Step -1
.Fields(i).Unlink
Next i
.SaveAs sPath & fName & Format(Order, "00#")
End With
End Sub


--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>