?
Solved

Macro or code to save file with name of field in mail merge - Word 2010

Posted on 2012-08-22
8
Medium Priority
?
954 Views
Last Modified: 2012-09-10
I have a Word merge form  (merge.docx) that contains a field called "company", along with a lot of other fields.

Let's say the data file  (mergedata.docx) has a company is called Testco (name will change often).

For the above example with company=Testco, I would like a macro to open the main merge document (merge.docx), merge all the records, then save the merged file as Testco.docx.

That's my preferred way of doing it.

If that can't be done, can the macro/code prompt me for the name to save the file?

Thanks!
Patty
0
Comment
Question by:Patty01Access
  • 4
  • 3
7 Comments
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 38322108
It sounds a little muddled. A result document from a Mail Merge would normally have multiple values for any particular field, so the save name would be hard to define.

Do you meant that you would want to run the merge only for records where the particular field has a given value, and then save that output document with that name?

You could do that programmatically by selecting only the particular records for the datasource e.g. by modifying the query (WHERE Company = 'Tesco'), then running it and saving ( doc.saveas "Tesco.docx").

Are you using a manually compiled word document as the datasource?
0
 

Author Comment

by:Patty01Access
ID: 38322260
I'm using Excel as the data source.  It looks like this:

Company     Building               Units
Testco           Union Arms          56
Testco           Bayview                 33
Testco           Holly Court            283

In the above, I would want the merged file with all 3 records saved as Testco.docx

The next time I run it, the data file might look like this:


Company     Building               Units
AnyCo           Walnut Manor        76
AnyCo           Ivy Court                44
AnyCo           Hillside                   28

In which case I would want the merged document saved as AnyCo.docx.

If that's too difficult, is there a way the code can prompt me for "Enter the file name to save"?
0
 
LVL 76

Accepted Solution

by:
GrahamSkan earned 2000 total points
ID: 38322771
This VBA macro code looks at the first record in the datasource and uses that for the file name:

Sub MergeAutoSave()
    Dim docMain As Document
    Dim docResult As Document
   
    Set docMain = Documents.Open("C:\MyMainFolder\MyMainDoc.docx")
    With docMain.MailMerge
        .DataSource.ActiveRecord = wdFirstDataSourceRecord
        strCustomer = .DataSource.DataFields("Customer").Value
        .Destination = wdSendToNewDocument
        .Execute
    End With
    docMain.Close wdDoNotSaveChanges
    Set docResult = ActiveDocument
    docResult.SaveAs "C:\MyFolder\" & strCustomer & ".docx"
    docResult.Close
End Sub
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

Author Comment

by:Patty01Access
ID: 38322831
Thanks.  I revised your code to change "customer" to "company"

    Dim docMain As Document
    Dim docResult As Document
   
    ChangeFileOpenDirectory "C:\Vacancy\Quest\Merge\Word\"
    Set docMain = Documents.Open("C:\Vacancy\Quest\Merge\Word\SurveyWord.doc")
    With docMain.MailMerge
        .DataSource.ActiveRecord = wdFirstDataSourceRecord
        strCompany = .DataSource.DataFields("Company").Value
        .Destination = wdSendToNewDocument
        .Execute
    End With
    docMain.Close wdDoNotSaveChanges
    Set docResult = ActiveDocument
    docResult.SaveAs "C:\Vacancy\Quest\Merge\Word\" & strCompany & ".docx"
    docResult.Close

But it bombs on the line:

      strCompany = .DataSource.DataFields("Company").Value


Saying: "Runtime error 5941:  The requested member of the collection does not exist."

I've attached my data file for you and merge document.
SurveyWord.doc
MergeWordData.xlsx
0
 
LVL 76

Assisted Solution

by:GrahamSkan
GrahamSkan earned 2000 total points
ID: 38322907
I am surprised to discover that, in this context, the capitalisation of the field name is pertinent.

This works:

 strCompany = .DataSource.DataFields("COMPANY").Value
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 38322915
That field name is all upper case in your datasource. I haven't seen case-sensitivity in field names elsewhere.
0
 

Author Comment

by:Patty01Access
ID: 38335874
That's it!!

Yes, who would have thought that the capitalization would even matter.

Thanks much!!!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you work with Word a lot, you probably use styles. If you use styles a lot, you've probably balled your fist more often than not when working with the ribbon. In Word 2007/2010, one of the things that I find missing when using styles is a quic…
Introduction This tutorial provides instructions on how to properly format your Word document using the inbuilt tools provided. The benefits of using these tools means your documents are more accessible and easily portable to other applications an…
This video shows and describes the main difference between both orientations in Microsoft Word. Viewers will understand when to use each orientation and how to get the most out of them.
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …
Suggested Courses
Course of the Month14 days, 14 hours left to enroll

840 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