Microsoft Office Forums Mailmerge Directory - Incomplete Data Appearing in Merge Output

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-02-2017, 09:54 AM
Gwinnett Gwinnett is offline Mailmerge Directory - Incomplete Data Appearing in Merge Output Windows 7 64bit Mailmerge Directory - Incomplete Data Appearing in Merge Output Office 2013
Novice
Mailmerge Directory - Incomplete Data Appearing in Merge Output
 
Join Date: Feb 2017
Posts: 5
Gwinnett is on a distinguished road
Default Mailmerge Directory - Incomplete Data Appearing in Merge Output

Hello,
I have successfully used the macros in Macropod’s excellent Catalogue/Mailmerge Directory Tutorial to create some very useful reports. Below is the merge code I used to merge text records by category for an FAQ report. I then use Macropod's TableJoiner macro on the output document to combine tables. I am using MS Word 2013.

The merge and macro worked fine. However, I found that 4 cells from the Excel worksheet did not merge completely. Only partial content was merged into the Word table. See attached PDF.



I suspected the data source because it was a file from Google Sheets. I have had difficulty merging Google spreadsheets in the past so I usually copy the data to a new worksheet in Excel. The client gave me a CSV file from Google Sheets, and I converted the file to XLSX in Excel.

I have reformatted the spreadsheet, cleared all content/format from the problem cells, and re-entered cell content for those cells. The same cells will not merge properly. I am stumped. I am hoping someone can identify why this is happening.

Cheers,
Attached Images
File Type: png Course FAQ Merge Code.PNG (48.4 KB, 18 views)
Attached Files
File Type: pdf FAQ Merge Error.pdf (43.9 KB, 3 views)
Reply With Quote
  #2  
Old 03-02-2017, 11:03 AM
Souriane Souriane is offline Mailmerge Directory - Incomplete Data Appearing in Merge Output Windows 7 64bit Mailmerge Directory - Incomplete Data Appearing in Merge Output Office 2013
Advanced Beginner
 
Join Date: Feb 2017
Location: Quebec, Canada
Posts: 60
Souriane is on a distinguished road
Default

Hi!

This is because there is more than 255 characters in your field. When you use OLE DB connection to do you merging, you can't have more than 255 characters.

Try using DDE connection.

Good luck!

Souriane
Reply With Quote
  #3  
Old 03-02-2017, 12:59 PM
Gwinnett Gwinnett is offline Mailmerge Directory - Incomplete Data Appearing in Merge Output Windows 7 64bit Mailmerge Directory - Incomplete Data Appearing in Merge Output Office 2013
Novice
Mailmerge Directory - Incomplete Data Appearing in Merge Output
 
Join Date: Feb 2017
Posts: 5
Gwinnett is on a distinguished road
Default

Thanks Souriane,

The lead was very helpful. I did a search and found a more detailed explanation and enabled the Confirm file format conversion on open check box (File > Options > Advanced> General) in MS Word.

That did the trick. Now all the spreadsheet content appears in the merged table output. I found this explanation most helpful: https://goo.gl/7sqfuO. If others are having similar a similar problem, start with this link.

Cheers,
Reply With Quote
  #4  
Old 03-02-2017, 03:01 PM
macropod's Avatar
macropod macropod is offline Mailmerge Directory - Incomplete Data Appearing in Merge Output Windows 7 64bit Mailmerge Directory - Incomplete Data Appearing in Merge Output Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,648
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Quote:
Originally Posted by Souriane View Post
This is because there is more than 255 characters in your field. When you use OLE DB connection to do you merging, you can't have more than 255 characters.
That's not correct.

When Word establishes the OLE DB connection, it tests the first ~8 records in each field to determine the data type. If those records contain text but none is more than 255 characters, Word treats it as a text field; if any of the records contains more than 255 characters, Word treats it as a memo field. Accordingly, all you need do in this case is ensure the first record, for example, contains more than 255 characters. You don't need to revert to the ancient DDE connection method.

This is all documented in the Mailmerge Tips & Tricks 'sticky' thread (https://www.msofficeforums.com/mail-...ps-tricks.html) at the top of this forum, under the heading Mailmerge Data Format Problems.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #5  
Old 03-03-2017, 12:38 PM
Gwinnett Gwinnett is offline Mailmerge Directory - Incomplete Data Appearing in Merge Output Windows 7 64bit Mailmerge Directory - Incomplete Data Appearing in Merge Output Office 2013
Novice
Mailmerge Directory - Incomplete Data Appearing in Merge Output
 
Join Date: Feb 2017
Posts: 5
Gwinnett is on a distinguished road
Default

Thanks, Macropod, for the clarification.

I did a test of your suggestion and found that it worked well. The merge output contained all the text and none of the records in the groups were truncated.

A couple of notes,

1. How to create a record that contains more than 255 characters? I used Word to create placeholder text (=Rand(1,4)) that ensured I would have sufficient text for the dummy record. See attached.

2. At first, I entered the dummy data in the first cell of the first column (Section) in the Excel sheet. That placement did not result in the proper output. I still had truncated cells in the output tables.

I am guessing that is because my merge code uses Key 1 (Mergefield Section) to group the records. Because my merge code uses Col A header as my Key 1 (Section), the placeholder text was appearing in the Key 1 mergefield and Key 2 mergefield (Question). See thread above for the merge code that I used.

3. When I put the placeholder text in the Key 2 (Question) field, the merge output was correct. See attached.

Inserting a dummy record in the first row of the Excel sheet is certainly easier than connecting to DDE files.

Cheers,
Attached Images
File Type: png =Rand(1,4).PNG (36.1 KB, 17 views)
File Type: png Mailmerge FAQs Excel DumbRec.PNG (30.2 KB, 16 views)
File Type: png Mailmerge FAQs Placeholder DumbRec.PNG (36.3 KB, 17 views)
Reply With Quote
  #6  
Old 03-05-2017, 02:24 PM
macropod's Avatar
macropod macropod is offline Mailmerge Directory - Incomplete Data Appearing in Merge Output Windows 7 64bit Mailmerge Directory - Incomplete Data Appearing in Merge Output Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,648
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Quote:
Originally Posted by Gwinnett View Post
A couple of notes,

1. How to create a record that contains more than 255 characters? I used Word to create placeholder text (=Rand(1,4)) that ensured I would have sufficient text for the dummy record.
Or, if it suits your situation, you might just re-organise your data so that a record with more than 255 characters occurs first.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
Reply

Tags
incomplete data, mailmerge, tables

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Mailmerge Directory - Incomplete Data Appearing in Merge Output Mailmerge Directory - TableJoiner Macro Gwinnett Mail Merge 3 03-01-2017 09:25 AM
Mailmerge Directory - Incomplete Data Appearing in Merge Output Word 2010 - directory mail merge - two separate group of data in one document htsu Mail Merge 2 03-07-2016 03:10 PM
Directory MailMerge with Category Grouped Lists on Labels screech Mail Merge 3 06-18-2014 11:41 PM
Pictorial directory using mailmerge JohnFinNC Mail Merge 6 10-04-2012 06:36 PM
Directory Mailmerge rohitjain80 Mail Merge 1 02-12-2012 03:42 AM


All times are GMT -7. The time now is 08:24 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft