Solved

Macro: Save Word Document as Merge Field

Posted on 2009-04-07
6
961 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
  • 3
  • 2
6 Comments
 
LVL 76

Assisted Solution

by:GrahamSkan
GrahamSkan earned 200 total points
Comment Utility
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
Comment Utility
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
Comment Utility
By the way, I'm using Word 2003 if that makes a difference.
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 76

Expert Comment

by:GrahamSkan
Comment Utility
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 300 total points
Comment Utility
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
Comment Utility
Thanks that works perfectly. Sorry for the slow reply, I was out of the office.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Microsoft Word is a program we have all encountered at some point, but very few of us have dug deep into its full scope of features, let alone customized it to suit our needs. Luckily making the ribbon (aka toolbar, first introduced in Word 2007) wo…
If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
This video teaches the viewer how to align pictures around text while keeping the text properly aligned in the document.
This video walks the viewer through the process of creating an MLA formatted document, as well as a bibliography with citations.

771 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now