Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Macro: Save Word Document as Merge Field

Posted on 2009-04-07
6
Medium Priority
?
1,002 Views
Last Modified: 2013-11-10
I have a large merge done from a .dat file into word. I would like to save each record in the merge as a seperate file. This is easy to do manually. However, I have so many I'm wondering if there is a way to get a macro to do this. I was able to suceed in getting that to work except I don't know how to name the document as a field name contained in the merge.

For example...
Child ID: CnBio_ID
First Name: First_Name
Last Name: Last_Name
etc.......

How can I make the macro save the file as what is in the CNBio_ID merge field?
Sub SaveAsSeperateDocument()
 
Dim x As Long
Dim i As Long
 
 With ActiveDocument.MailMerge
   
   .Destination = wdSendToNewDocument
   .SuppressBlankLines = True
   
   'get the record count of the datasource
   With .DataSource
     .ActiveRecord = wdLastRecord
     x = .ActiveRecord
     'set the activerecord back to the first
     .ActiveRecord = wdFirstRecord
    
   End With
   
   'loop the datasource count and merge one record at a time
   For i = 1 To x
     .DataSource.FirstRecord = i
     .DataSource.LastRecord = i
     .Execute Pause:=True
 
///////////////////////// PROBLEM IS HERE ///////////////////////////////////////    
     ChildID = MERGEFIELD CnBio_ID
         
      ActiveDocument.SaveAs FileName:=ChildID
/////////////////////////////////////////////////////////////////////////////////////////////
 
      ActiveDocument.Close
   
   Next i
 End With
End Sub

Open in new window

0
Comment
Question by:NovaRising
[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
  • 2
6 Comments
 
LVL 76

Assisted Solution

by:GrahamSkan
GrahamSkan earned 800 total points
ID: 24091030
This is a macro that I have used in the past.
Sub MergeToSingles()
Dim wrdMainDoc As Word.Document
Dim l As Integer
 
Set wrdMainDoc = ActiveDocument
With wrdMainDoc.MailMerge
    For l = 1 To .DataSource.RecordCount
        .Destination = wdSendToNewDocument
        .DataSource.FirstRecord = l
        .DataSource.LastRecord = l
        .Execute
        .DataSource.ActiveRecord = l
        ActiveDocument.SaveAs .DataSource.DataFields("Last_Name") & ".doc"
        ActiveDocument.Close wdDoNotSaveChanges
    Next l
End With
End Sub

Open in new window

0
 
LVL 3

Author Comment

by:NovaRising
ID: 24091533
I wasn't able to get that to work. Might it place the documents in some location? It didn't appear to do anything.
Taking what you did have there I was able to come up with the following that will save the first document but says "it cannot save the next document with the same name".

Sub MergeAllRecordsToPrinter()
 
'
 
' MergeAllRecordsToPrinter Macro
 
 
 
 
 
Dim x As Long
 
Dim i As Long
 
 
 
 With ActiveDocument.MailMerge
 
   
 
   .Destination = wdSendToNewDocument
 
   .SuppressBlankLines = True
 
   
 
   'get the record count of the datasource
 
   With .DataSource
 
     .ActiveRecord = wdLastRecord
 
     x = .ActiveRecord
 
     'set the activerecord back to the first
 
     .ActiveRecord = wdFirstRecord
 
    
 
   End With
 
   
 
   'loop the datasource count and merge one record at a time
 
   For i = 1 To x
 
     .DataSource.FirstRecord = i
 
     .DataSource.LastRecord = i
 
     .Execute Pause:=True
 
       ActiveDocument.SaveAs .DataSource.DataFields("CnBio_ID") & ".doc"
        ActiveDocument.Close wdDoNotSaveChanges
 
 
   
 
   Next i
 
 End With
 
End Sub

Open in new window

0
 
LVL 3

Author Comment

by:NovaRising
ID: 24091547
By the way, I'm using Word 2003 if that makes a difference.
0
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!

 
LVL 76

Expert Comment

by:GrahamSkan
ID: 24091570
I think that the rest of the message is: 'as an open document'.

Make sure that all earlier results documents are closed.
0
 
LVL 23

Accepted Solution

by:
irudyk earned 1200 total points
ID: 24092278
It seems like you are not getting the CnBio_ID value of the current record you are merging.  Using the code you post along with that from GrahamSkan, try the following code to see if that corrects the issue.
Sub MergeAllRecordsToPrinter()
 
'MergeAllRecordsToPrinter Macro
Dim x As Long
Dim i As Long
 
With ActiveDocument.MailMerge
     .Destination = wdSendToNewDocument
     .SuppressBlankLines = True
     'get the record count of the datasource
     With .DataSource
          .ActiveRecord = wdLastRecord
          x = .ActiveRecord
          'set the activerecord back to the first
          .ActiveRecord = wdFirstRecord
     End With
 
     'loop the datasource count and merge one record at a time
     For i = 1 To x
 
          .DataSource.FirstRecord = i
          .DataSource.LastRecord = i
          .Execute Pause:=True
 
          'set active record to the one that was just merged
          .DataSource.ActiveRecord = i
 
          ActiveDocument.SaveAs .DataSource.DataFields("CnBio_ID") & ".doc"
          ActiveDocument.Close wdDoNotSaveChanges
 
     Next i
 
End With
 
End Sub

Open in new window

0
 
LVL 3

Author Closing Comment

by:NovaRising
ID: 31567698
Thanks that works perfectly. Sorry for the slow reply, I was out of the office.
0

Featured Post

Industry Leaders: 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

In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.
Computer science students often experience many of the same frustrations when going through their engineering courses. This article presents seven tips I found useful when completing a bachelors and masters degree in computing which I believe may he…
This Micro Tutorial well show you how to find and replace special characters in Microsoft Word. This is similar to carriage returns to convert columns of values from Microsoft Excel into comma separated lists.
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 …

719 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