Split a 500 page merge into about 23 documents

I've read and tested the all suggested split code and plugins but they seem to be designed to split into individual docs, I've got about 500 labels and they're a bit unwieldy as a single document, they'd be best split into each location (roughly 23 documents) which can be determined each time that location field changes, which would also be the file name, is anyone able to help? Thanks

I've just confirmed the addins that say [many to one] don't work for my use case either they seem to be designed to put multiple merge records into a single page or table, I've got a small label entirely filled by it's own data on a table, I want 20 label pages per document

Figured it out for anyone else passing through - thanks to Macropod over at MrExcel - this is his code suggestion with a chunk of my bodge around it to select a range to output on the merge rather than a single

Code:

---

Sub MergeToSplitLine()



    'Merges one record at a time to the chosen output folder
'Application.ScreenUpdating = False
Dim StrFolder As String, StrName As String, MainDoc As Document, i As Long, istart As Long, thisSet As String
Set MainDoc = ActiveDocument
With MainDoc
StrFolder = .Path & "\"


.MailMerge.DataSource.ActiveRecord = 1
thisSet = .MailMerge.DataSource.DataFields("Line").Value
StrName = thisSet & " _ " & .MailMerge.DataSource.DataFields("Part")
istart = 1
'MsgBox "start"


  For i = 1 To .MailMerge.DataSource.RecordCount
   
   
   
    If i < .MailMerge.DataSource.RecordCount Then
    .MailMerge.DataSource.ActiveRecord = i + 1
        If thisSet = .MailMerge.DataSource.DataFields("Line").Value Then
        GoTo skipOutput
        End If
    End If


        With .MailMerge
          .Destination = wdSendToNewDocument
          .SuppressBlankLines = True
          With .DataSource
            .FirstRecord = istart
            .LastRecord = i
          End With
          .Execute Pause:=False
        End With
        With ActiveDocument
          .SaveAs2 FileName:=StrFolder & StrName & ".docx", FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False
          .Close SaveChanges:=False
        End With
        If i < .MailMerge.DataSource.RecordCount Then
            istart = i + 1
            .MailMerge.DataSource.ActiveRecord = i + 1
            thisSet = .MailMerge.DataSource.DataFields("Line").Value
            StrName = thisSet & " _ " & .MailMerge.DataSource.DataFields("Part")
        End If
skipOutput:
   
    DoEvents
 
  Next i
End With




Application.ScreenUpdating = True

---

edit: some issues with getting the last page to end up in the right document especially if it was on it's own

Well done!! Thank you for sharing your code as well.

Thank you for sharing this.
For cross-posting etiquette, please read: A Message to Forum Cross-Posters https://www.excelguru.ca/content.php?184

In the future, I think what I would suggest would be doing twenty-three merges, filtering for the location.

Quote:

---

Originally Posted by Chas Kenyon

Thank you for sharing this.
For cross-posting etiquette, please read: A Message to Forum Cross-Posters https://www.excelguru.ca/content.php?184

In the future, I think what I would suggest would be doing twenty-three merges, filtering for the location.

---

Apologies for the cross post without links, should have thought of that! :-)

how do you mean about 23 merges? I did wonder about filtering on location but I didn't see any way to get the list of locations (they're always different) into an array or something to then loop through?

If the location is a data field, only merge records that match a particular field for one of your locations.
You could probably even record a macro that does this.

Ok, but I want all 500 pages exported at once, just split up, I'm unsure how filters would help with that? or do you mean automate the export with VBA but using filters to select the values? this is where I meant "I didn't see any way to get the list of locations (they're always different) into an array or something to then loop through?"

My suggestion was for a manual solution. 23 separate merges.
You could record doing this in one macro.
You would end up with 23 separate documents (i.e. the result you were trying to accomplish) without the 500 page start.
You never have all of them in one document.

Yes, a macro could read your data file and find how many distinct locations there are, put them in an array and loop through them.
This is beyond my skillset for an easy project.

Ahh the locations are different every time, up to a couple of hundred, pulled from a list of over 1000, 23 was a smallest case example I was using for test and development

Yea I got to that conclusion as well, Set dotnetarray = CreateObject("System.Collections.ArrayList") gives a useful array but I was unsure how to populate it from the data source? I could find the query string "Select * etc etc" and assumed something like "select location from etc etc" but I'm from Access land and there aren't really recordsets in Word so I'm a bit like a fish out of water

Speaking as an Access novice, I think I would use Access to generate the reports, possibly csv files, by location.