Saving the records of a mail merged document to sequentially numbers files 
Author Message
 Saving the records of a mail merged document to sequentially numbers files

Hi Everyone

I've been using the following VBA (thanks to MVP) to save the record of a
mail merged document as separate files, however I've come across a little
problem and my VBA skills aren't good enough to fix it.

PROBLEM:
The source mail merge documents are all stored in the same director (this
can't be changed due to the way the database works).  So if I open one of
the mail merged documents and run the VBA on it the separate files are saved
in the same directory.  The problem comes when I try to run the VBA on
another mail merge document in the same directory, as the new files
overwrite the previous files.

Is it possible for one of you to help me modify the code so that the
sequential numbering section remembers the number of the last file to be
save.  The MVP site has a similar piece of VBA to allow sequentially
numbered invoices, and it uses a text file as the source for the numbering,
however as I said earlier, my VBA skills aren't that good and I need some
help please.

Sub SaveRecsAsFiles()

    ' Convert all sections to Subdocs

    AllSectionsToSubDoc ActiveDocument

    'Save each Subdoc as a separate file

    SaveAllSubDocs ActiveDocument

End Sub

Sub AllSectionsToSubDoc(ByRef doc As Word.Document)

    Dim secCounter As Long

    Dim NrSecs As Long

    NrSecs = doc.Sections.Count

    'Start from the end because creating

    'Subdocs inserts additional sections

    For secCounter = NrSecs - 1 To 1 Step -1

        doc.Subdocuments.AddFromRange _

            doc.Sections(secCounter).Range

    Next secCounter

End Sub

Sub SaveAllSubDocs(ByRef doc As Word.Document)

    Dim subdoc As Word.Subdocument

    Dim newdoc As Word.Document

    Dim docCounter As Long

    docCounter = 1

    'Must be in MasterView to work with

    'Subdocs as separate files

    doc.ActiveWindow.View = wdMasterView

    For Each subdoc In doc.Subdocuments

        Set newdoc = subdoc.Open

        'Remove NextPage section breaks

        'originating from mailmerge

        RemoveAllSectionBreaks newdoc

        With newdoc

            .SaveAs FileName:="MergeResult" & CStr(docCounter)

            .Close

        End With

        docCounter = docCounter + 1

    Next subdoc

End Sub

Sub RemoveAllSectionBreaks(doc As Word.Document)

    With doc.Range.Find

        .ClearFormatting

        .Text = "^b"

        With .Replacement

            .ClearFormatting

            .Text = ""

        End With

        .Execute Replace:=wdReplaceAll

    End With

End Sub

Big thank you in advance

Al



Tue, 22 Mar 2005 02:52:55 GMT  
 Saving the records of a mail merged document to sequentially numbers files
Hi Al,

The following modified  Sub SaveAllSubDocs(ByRef doc As Word.Document)
should do what you want:

Sub SaveAllSubDocs(ByRef doc As Word.Document)
    Dim subdoc As Word.Subdocument
    Dim newdoc As Word.Document
    Dim docCounter As Long
    'Get the number of the last saved document
    docCounter = System.PrivateProfileString("C:\Settings.Txt",
"MacroSettings", "docCounter")
    'If there is no number there, set the number docCounter = 1
    If docCounter = "" Then
       docCounter = 1
    End If
    'Must be in MasterView to work with
    'Subdocs as separate files
    doc.ActiveWindow.View = wdMasterView
    For Each subdoc In doc.Subdocuments
        Set newdoc = subdoc.Open
        'Remove NextPage section breaks
        'originating from mailmerge
        RemoveAllSectionBreaks newdoc
        With newdoc
            .SaveAs FileName:="MergeResult" & CStr(docCounter)
            .Close
        End With
        docCounter = docCounter + 1
    Next subdoc
    'Save the next number back to the Settings.txt file ready for the next
use.
    System.PrivateProfileString("C:\Settings.txt", "MacroSettings", "
docCounter ") = docCounter
End Sub

Please post any response to the newsgroups for the benefit of others who may
also be following the thread.

Hope this helps,
Doug Robbins - Word MVP

Quote:
> Hi Everyone

> I've been using the following VBA (thanks to MVP) to save the record of a
> mail merged document as separate files, however I've come across a little
> problem and my VBA skills aren't good enough to fix it.

> PROBLEM:
> The source mail merge documents are all stored in the same director (this
> can't be changed due to the way the database works).  So if I open one of
> the mail merged documents and run the VBA on it the separate files are
saved
> in the same directory.  The problem comes when I try to run the VBA on
> another mail merge document in the same directory, as the new files
> overwrite the previous files.

> Is it possible for one of you to help me modify the code so that the
> sequential numbering section remembers the number of the last file to be
> save.  The MVP site has a similar piece of VBA to allow sequentially
> numbered invoices, and it uses a text file as the source for the
numbering,
> however as I said earlier, my VBA skills aren't that good and I need some
> help please.

> Sub SaveRecsAsFiles()

>     ' Convert all sections to Subdocs

>     AllSectionsToSubDoc ActiveDocument

>     'Save each Subdoc as a separate file

>     SaveAllSubDocs ActiveDocument

> End Sub

> Sub AllSectionsToSubDoc(ByRef doc As Word.Document)

>     Dim secCounter As Long

>     Dim NrSecs As Long

>     NrSecs = doc.Sections.Count

>     'Start from the end because creating

>     'Subdocs inserts additional sections

>     For secCounter = NrSecs - 1 To 1 Step -1

>         doc.Subdocuments.AddFromRange _

>             doc.Sections(secCounter).Range

>     Next secCounter

> End Sub

> Sub SaveAllSubDocs(ByRef doc As Word.Document)

>     Dim subdoc As Word.Subdocument

>     Dim newdoc As Word.Document

>     Dim docCounter As Long

>     docCounter = 1

>     'Must be in MasterView to work with

>     'Subdocs as separate files

>     doc.ActiveWindow.View = wdMasterView

>     For Each subdoc In doc.Subdocuments

>         Set newdoc = subdoc.Open

>         'Remove NextPage section breaks

>         'originating from mailmerge

>         RemoveAllSectionBreaks newdoc

>         With newdoc

>             .SaveAs FileName:="MergeResult" & CStr(docCounter)

>             .Close

>         End With

>         docCounter = docCounter + 1

>     Next subdoc

> End Sub

> Sub RemoveAllSectionBreaks(doc As Word.Document)

>     With doc.Range.Find

>         .ClearFormatting

>         .Text = "^b"

>         With .Replacement

>             .ClearFormatting

>             .Text = ""

>         End With

>         .Execute Replace:=wdReplaceAll

>     End With

> End Sub

> Big thank you in advance

> Al



Thu, 24 Mar 2005 07:10:46 GMT  
 Saving the records of a mail merged document to sequentially numbers files
Hi Doug

Many thanks for the response, sorry about placing the question on two
groups, new to using news groups.

I've tried the new code and get a compile error on the following line

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

Any ideas

Al



Quote:
> Hi Al,

> The following modified  Sub SaveAllSubDocs(ByRef doc As Word.Document)
> should do what you want:

> Sub SaveAllSubDocs(ByRef doc As Word.Document)
>     Dim subdoc As Word.Subdocument
>     Dim newdoc As Word.Document
>     Dim docCounter As Long
>     'Get the number of the last saved document
>     docCounter = System.PrivateProfileString("C:\Settings.Txt",
> "MacroSettings", "docCounter")
>     'If there is no number there, set the number docCounter = 1
>     If docCounter = "" Then
>        docCounter = 1
>     End If
>     'Must be in MasterView to work with
>     'Subdocs as separate files
>     doc.ActiveWindow.View = wdMasterView
>     For Each subdoc In doc.Subdocuments
>         Set newdoc = subdoc.Open
>         'Remove NextPage section breaks
>         'originating from mailmerge
>         RemoveAllSectionBreaks newdoc
>         With newdoc
>             .SaveAs FileName:="MergeResult" & CStr(docCounter)
>             .Close
>         End With
>         docCounter = docCounter + 1
>     Next subdoc
>     'Save the next number back to the Settings.txt file ready for the next
> use.
>     System.PrivateProfileString("C:\Settings.txt", "MacroSettings", "
> docCounter ") = docCounter
> End Sub

> Please post any response to the newsgroups for the benefit of others who
may
> also be following the thread.

> Hope this helps,
> Doug Robbins - Word MVP


> > Hi Everyone

> > I've been using the following VBA (thanks to MVP) to save the record of
a
> > mail merged document as separate files, however I've come across a
little
> > problem and my VBA skills aren't good enough to fix it.

> > PROBLEM:
> > The source mail merge documents are all stored in the same director
(this
> > can't be changed due to the way the database works).  So if I open one
of
> > the mail merged documents and run the VBA on it the separate files are
> saved
> > in the same directory.  The problem comes when I try to run the VBA on
> > another mail merge document in the same directory, as the new files
> > overwrite the previous files.

> > Is it possible for one of you to help me modify the code so that the
> > sequential numbering section remembers the number of the last file to be
> > save.  The MVP site has a similar piece of VBA to allow sequentially
> > numbered invoices, and it uses a text file as the source for the
> numbering,
> > however as I said earlier, my VBA skills aren't that good and I need
some
> > help please.

> > Sub SaveRecsAsFiles()

> >     ' Convert all sections to Subdocs

> >     AllSectionsToSubDoc ActiveDocument

> >     'Save each Subdoc as a separate file

> >     SaveAllSubDocs ActiveDocument

> > End Sub

> > Sub AllSectionsToSubDoc(ByRef doc As Word.Document)

> >     Dim secCounter As Long

> >     Dim NrSecs As Long

> >     NrSecs = doc.Sections.Count

> >     'Start from the end because creating

> >     'Subdocs inserts additional sections

> >     For secCounter = NrSecs - 1 To 1 Step -1

> >         doc.Subdocuments.AddFromRange _

> >             doc.Sections(secCounter).Range

> >     Next secCounter

> > End Sub

> > Sub SaveAllSubDocs(ByRef doc As Word.Document)

> >     Dim subdoc As Word.Subdocument

> >     Dim newdoc As Word.Document

> >     Dim docCounter As Long

> >     docCounter = 1

> >     'Must be in MasterView to work with

> >     'Subdocs as separate files

> >     doc.ActiveWindow.View = wdMasterView

> >     For Each subdoc In doc.Subdocuments

> >         Set newdoc = subdoc.Open

> >         'Remove NextPage section breaks

> >         'originating from mailmerge

> >         RemoveAllSectionBreaks newdoc

> >         With newdoc

> >             .SaveAs FileName:="MergeResult" & CStr(docCounter)

> >             .Close

> >         End With

> >         docCounter = docCounter + 1

> >     Next subdoc

> > End Sub

> > Sub RemoveAllSectionBreaks(doc As Word.Document)

> >     With doc.Range.Find

> >         .ClearFormatting

> >         .Text = "^b"

> >         With .Replacement

> >             .ClearFormatting

> >             .Text = ""

> >         End With

> >         .Execute Replace:=wdReplaceAll

> >     End With

> > End Sub

> > Big thank you in advance

> > Al



Fri, 25 Mar 2005 01:08:26 GMT  
 Saving the records of a mail merged document to sequentially numbers files
Hi Harley,

That code statement needs to be all on one line place the cursor at the end
of the first line and press the delete key to remove the carriage return
that has been inserted by the mail program.

Please post any response to the newsgroups for the benefit of others who may
also be following the thread.

Hope this helps,
Doug Robbins - Word MVP

Quote:
> Hi Doug

> Many thanks for the response, sorry about placing the question on two
> groups, new to using news groups.

> I've tried the new code and get a compile error on the following line

>     docCounter = System.PrivateProfileString("C:\Settings.Txt",
> "MacroSettings", "docCounter")

> Any ideas

> Al



> > Hi Al,

> > The following modified  Sub SaveAllSubDocs(ByRef doc As Word.Document)
> > should do what you want:

> > Sub SaveAllSubDocs(ByRef doc As Word.Document)
> >     Dim subdoc As Word.Subdocument
> >     Dim newdoc As Word.Document
> >     Dim docCounter As Long
> >     'Get the number of the last saved document
> >     docCounter = System.PrivateProfileString("C:\Settings.Txt",
> > "MacroSettings", "docCounter")
> >     'If there is no number there, set the number docCounter = 1
> >     If docCounter = "" Then
> >        docCounter = 1
> >     End If
> >     'Must be in MasterView to work with
> >     'Subdocs as separate files
> >     doc.ActiveWindow.View = wdMasterView
> >     For Each subdoc In doc.Subdocuments
> >         Set newdoc = subdoc.Open
> >         'Remove NextPage section breaks
> >         'originating from mailmerge
> >         RemoveAllSectionBreaks newdoc
> >         With newdoc
> >             .SaveAs FileName:="MergeResult" & CStr(docCounter)
> >             .Close
> >         End With
> >         docCounter = docCounter + 1
> >     Next subdoc
> >     'Save the next number back to the Settings.txt file ready for the
next
> > use.
> >     System.PrivateProfileString("C:\Settings.txt", "MacroSettings", "
> > docCounter ") = docCounter
> > End Sub

> > Please post any response to the newsgroups for the benefit of others who
> may
> > also be following the thread.

> > Hope this helps,
> > Doug Robbins - Word MVP


> > > Hi Everyone

> > > I've been using the following VBA (thanks to MVP) to save the record
of
> a
> > > mail merged document as separate files, however I've come across a
> little
> > > problem and my VBA skills aren't good enough to fix it.

> > > PROBLEM:
> > > The source mail merge documents are all stored in the same director
> (this
> > > can't be changed due to the way the database works).  So if I open one
> of
> > > the mail merged documents and run the VBA on it the separate files are
> > saved
> > > in the same directory.  The problem comes when I try to run the VBA on
> > > another mail merge document in the same directory, as the new files
> > > overwrite the previous files.

> > > Is it possible for one of you to help me modify the code so that the
> > > sequential numbering section remembers the number of the last file to
be
> > > save.  The MVP site has a similar piece of VBA to allow sequentially
> > > numbered invoices, and it uses a text file as the source for the
> > numbering,
> > > however as I said earlier, my VBA skills aren't that good and I need
> some
> > > help please.

> > > Sub SaveRecsAsFiles()

> > >     ' Convert all sections to Subdocs

> > >     AllSectionsToSubDoc ActiveDocument

> > >     'Save each Subdoc as a separate file

> > >     SaveAllSubDocs ActiveDocument

> > > End Sub

> > > Sub AllSectionsToSubDoc(ByRef doc As Word.Document)

> > >     Dim secCounter As Long

> > >     Dim NrSecs As Long

> > >     NrSecs = doc.Sections.Count

> > >     'Start from the end because creating

> > >     'Subdocs inserts additional sections

> > >     For secCounter = NrSecs - 1 To 1 Step -1

> > >         doc.Subdocuments.AddFromRange _

> > >             doc.Sections(secCounter).Range

> > >     Next secCounter

> > > End Sub

> > > Sub SaveAllSubDocs(ByRef doc As Word.Document)

> > >     Dim subdoc As Word.Subdocument

> > >     Dim newdoc As Word.Document

> > >     Dim docCounter As Long

> > >     docCounter = 1

> > >     'Must be in MasterView to work with

> > >     'Subdocs as separate files

> > >     doc.ActiveWindow.View = wdMasterView

> > >     For Each subdoc In doc.Subdocuments

> > >         Set newdoc = subdoc.Open

> > >         'Remove NextPage section breaks

> > >         'originating from mailmerge

> > >         RemoveAllSectionBreaks newdoc

> > >         With newdoc

> > >             .SaveAs FileName:="MergeResult" & CStr(docCounter)

> > >             .Close

> > >         End With

> > >         docCounter = docCounter + 1

> > >     Next subdoc

> > > End Sub

> > > Sub RemoveAllSectionBreaks(doc As Word.Document)

> > >     With doc.Range.Find

> > >         .ClearFormatting

> > >         .Text = "^b"

> > >         With .Replacement

> > >             .ClearFormatting

> > >             .Text = ""

> > >         End With

> > >         .Execute Replace:=wdReplaceAll

> > >     End With

> > > End Sub

> > > Big thank you in advance

> > > Al



Fri, 25 Mar 2005 16:47:51 GMT  
 Saving the records of a mail merged document to sequentially numbers files
Hi Doug

Many Thanks, that worked well.

Harley



Quote:
> Hi Harley,

> That code statement needs to be all on one line place the cursor at the
end
> of the first line and press the delete key to remove the carriage return
> that has been inserted by the mail program.

> Please post any response to the newsgroups for the benefit of others who
may
> also be following the thread.

> Hope this helps,
> Doug Robbins - Word MVP


> > Hi Doug

> > Many thanks for the response, sorry about placing the question on two
> > groups, new to using news groups.

> > I've tried the new code and get a compile error on the following line

> >     docCounter = System.PrivateProfileString("C:\Settings.Txt",
> > "MacroSettings", "docCounter")

> > Any ideas

> > Al



> > > Hi Al,

> > > The following modified  Sub SaveAllSubDocs(ByRef doc As Word.Document)
> > > should do what you want:

> > > Sub SaveAllSubDocs(ByRef doc As Word.Document)
> > >     Dim subdoc As Word.Subdocument
> > >     Dim newdoc As Word.Document
> > >     Dim docCounter As Long
> > >     'Get the number of the last saved document
> > >     docCounter = System.PrivateProfileString("C:\Settings.Txt",
> > > "MacroSettings", "docCounter")
> > >     'If there is no number there, set the number docCounter = 1
> > >     If docCounter = "" Then
> > >        docCounter = 1
> > >     End If
> > >     'Must be in MasterView to work with
> > >     'Subdocs as separate files
> > >     doc.ActiveWindow.View = wdMasterView
> > >     For Each subdoc In doc.Subdocuments
> > >         Set newdoc = subdoc.Open
> > >         'Remove NextPage section breaks
> > >         'originating from mailmerge
> > >         RemoveAllSectionBreaks newdoc
> > >         With newdoc
> > >             .SaveAs FileName:="MergeResult" & CStr(docCounter)
> > >             .Close
> > >         End With
> > >         docCounter = docCounter + 1
> > >     Next subdoc
> > >     'Save the next number back to the Settings.txt file ready for the
> next
> > > use.
> > >     System.PrivateProfileString("C:\Settings.txt", "MacroSettings", "
> > > docCounter ") = docCounter
> > > End Sub

> > > Please post any response to the newsgroups for the benefit of others
who
> > may
> > > also be following the thread.

> > > Hope this helps,
> > > Doug Robbins - Word MVP


> > > > Hi Everyone

> > > > I've been using the following VBA (thanks to MVP) to save the record
> of
> > a
> > > > mail merged document as separate files, however I've come across a
> > little
> > > > problem and my VBA skills aren't good enough to fix it.

> > > > PROBLEM:
> > > > The source mail merge documents are all stored in the same director
> > (this
> > > > can't be changed due to the way the database works).  So if I open
one
> > of
> > > > the mail merged documents and run the VBA on it the separate files
are
> > > saved
> > > > in the same directory.  The problem comes when I try to run the VBA
on
> > > > another mail merge document in the same directory, as the new files
> > > > overwrite the previous files.

> > > > Is it possible for one of you to help me modify the code so that the
> > > > sequential numbering section remembers the number of the last file
to
> be
> > > > save.  The MVP site has a similar piece of VBA to allow sequentially
> > > > numbered invoices, and it uses a text file as the source for the
> > > numbering,
> > > > however as I said earlier, my VBA skills aren't that good and I need
> > some
> > > > help please.

> > > > Sub SaveRecsAsFiles()

> > > >     ' Convert all sections to Subdocs

> > > >     AllSectionsToSubDoc ActiveDocument

> > > >     'Save each Subdoc as a separate file

> > > >     SaveAllSubDocs ActiveDocument

> > > > End Sub

> > > > Sub AllSectionsToSubDoc(ByRef doc As Word.Document)

> > > >     Dim secCounter As Long

> > > >     Dim NrSecs As Long

> > > >     NrSecs = doc.Sections.Count

> > > >     'Start from the end because creating

> > > >     'Subdocs inserts additional sections

> > > >     For secCounter = NrSecs - 1 To 1 Step -1

> > > >         doc.Subdocuments.AddFromRange _

> > > >             doc.Sections(secCounter).Range

> > > >     Next secCounter

> > > > End Sub

> > > > Sub SaveAllSubDocs(ByRef doc As Word.Document)

> > > >     Dim subdoc As Word.Subdocument

> > > >     Dim newdoc As Word.Document

> > > >     Dim docCounter As Long

> > > >     docCounter = 1

> > > >     'Must be in MasterView to work with

> > > >     'Subdocs as separate files

> > > >     doc.ActiveWindow.View = wdMasterView

> > > >     For Each subdoc In doc.Subdocuments

> > > >         Set newdoc = subdoc.Open

> > > >         'Remove NextPage section breaks

> > > >         'originating from mailmerge

> > > >         RemoveAllSectionBreaks newdoc

> > > >         With newdoc

> > > >             .SaveAs FileName:="MergeResult" & CStr(docCounter)

> > > >             .Close

> > > >         End With

> > > >         docCounter = docCounter + 1

> > > >     Next subdoc

> > > > End Sub

> > > > Sub RemoveAllSectionBreaks(doc As Word.Document)

> > > >     With doc.Range.Find

> > > >         .ClearFormatting

> > > >         .Text = "^b"

> > > >         With .Replacement

> > > >             .ClearFormatting

> > > >             .Text = ""

> > > >         End With

> > > >         .Execute Replace:=wdReplaceAll

> > > >     End With

> > > > End Sub

> > > > Big thank you in advance

> > > > Al



Sat, 26 Mar 2005 04:16:49 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. Saving Mail Merge documents

2. Saving a Merge document as seperate files.

3. (Q211727) Mail merge document restored to normal document - Word 2000

4. Saving mail merge pages as separate files

5. Mail Merge BarCodes / Mail Merge Insertion Point

6. e-mail from Access via does not send merged Word document

7. Mail Merge to a pre-existing word document via a macro

8. Adding a picture into a word document while doing a mail merge

9. Detect closure of child mail merge document

10. Removing mail merge data source when opening a document

11. How to change document format in Mail Merge???

12. Finding Mail Merge Data Documents

 

 
Powered by phpBB® Forum Software