PDA

View Full Version : Split a 500 page merge into about 23 documents



documntimpos
02-16-2024, 02:32 AM
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

documntimpos
02-16-2024, 07:40 AM
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

documntimpos
02-16-2024, 05:13 PM
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


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

jdelano
02-17-2024, 06:31 AM
Well done!! Thank you for sharing your code as well.

Chas Kenyon
02-17-2024, 07:59 PM
Thank you for sharing this.
For cross-posting etiquette, please read: A Message to Forum Cross-Posters (https://www.excelguru.ca/content.php?184) 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.

documntimpos
02-19-2024, 08:26 AM
Thank you for sharing this.
For cross-posting etiquette, please read: A Message to Forum Cross-Posters (https://www.excelguru.ca/content.php?184) 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?

Chas Kenyon
02-19-2024, 08:11 PM
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.

documntimpos
02-20-2024, 03:33 AM
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?"

Chas Kenyon
02-20-2024, 03:09 PM
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.

documntimpos
02-20-2024, 03:38 PM
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

Chas Kenyon
02-25-2024, 01:58 PM
Speaking as an Access novice, I think I would use Access to generate the reports, possibly csv files, by location.