Problems with converting data held in an Excel spreadsheet to Word

[mark Gray]

I am using MS Office 2000 Professional, and have a lot of mail merge documents where I have all the required data in an Excel spreadsheet. I then try and complete mail merges on a selection of the data.

I then copy and paste the spreadsheet into a word document to reformat the data, and then complete the merge.

However, word ignores the reformatting, and the deselected data, and constantly brings up the warning 'too few data items'.

This causes the mail merge to fail. As a fallback I use another PC with an earlier version of Office, and this procedure works perfectly, however this is time consuming and eventually these other PCs will be phased out.

Solutions to this problem would be very weelcome.

Thank you

 

[Cindy M -WordMVP-]

Hi =?Utf-8?B?bWFyayBHcmF5?=,

I'm afraid you're not giving us enough detail (the kind we'd get looking over your
shoulder).

1. How are you selecting (filtering) the data you want to use for the mail merge? Can you
give us an example?

2. I don't understand the part about copying and pasting the spreadsheet into Word. What
kind of reformatting? Are you then using the WORD TABLE as the data source? Is it still
linked back to Excel?

3. Have you tried linking up to the data soruce file (whichever it is) again? The error
message sounds as if Word is expecting more fields than you're providing...

I am using MS Office 2000 Professional, and have a lot of mail merge documents where I

have all the required data in an Excel spreadsheet. I then try and complete mail merges on
a selection of the data.

I then copy and paste the spreadsheet into a word document to reformat the data, and then complete the merge.

However, word ignores the reformatting, and the deselected data, and constantly brings up

the warning 'too few data items'.

This causes the mail merge to fail. As a fallback I use another PC with an earlier

version of Office, and this procedure works perfectly, however this is time consuming and
eventually these other PCs will be phased out.
Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply in the
newsgroup and not by e-mail

 

[mark Gray]

Cindy M
Apologies for not enough information before. Heres a full explanation of the problem:-

I collect a lot of data in excel, including field headings such as Title, Initials, Surname, Address line 1, Address line 2 ........ Scheme, Annual Amount .. etc..
This data is extracted from a separate application.

I then filter the data (using auto filter), so I say only get members of scheme 1 not scheme 2 and 3.

I then copy and paste this data to a new word document, where I format the Name and Address field columns (singularly rather than in bulk) so that all alpha fields are 'title case' sensitive.

I then try and do a mail merge in word to this data, and get the word error messages about missing cell data (ie one record may not have an entry in address line 4, but others do), and also I get all schemes, rather than just scheme 1, and also the reformatting to 'title case' is reverted back to the original case.

I could link to the original data in Excel, but then again have problems with reformatting the data, and with the selection process.

I think that is about all.

Your assistance would be most welcome.

Regards

 

[Cindy M -WordMVP-]

Hi =?Utf-8?B?bWFyayBHcmF5?=,

Ah, I understand what's happening, I think.

You're apparently doing a PasteLINK to the data in Excel. But the link is going to carry
across ALL the information, and Word will try to reconcile the records that aren't visible.

You need to paste the table into Word without a link.

I collect a lot of data in excel, including field headings such as Title, Initials,

Surname, Address line 1, Address line 2 ........ Scheme, Annual Amount .. etc..

This data is extracted from a separate application.

I then filter the data (using auto filter), so I say only get members of scheme 1 not scheme 2 and 3.

I then copy and paste this data to a new word document, where I format the Name and

Address field columns (singularly rather than in bulk) so that all alpha fields are 'title
case' sensitive.

I then try and do a mail merge in word to this data, and get the word error messages

about missing cell data (ie one record may not have an entry in address line 4, but others
do), and also I get all schemes, rather than just scheme 1, and also the reformatting to
'title case' is reverted back to the original case.
Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply in the
newsgroup and not by e-mail

 

[Cindy M -WordMVP-]

Hi =?Utf-8?B?bWFyayBHcmF5?=,

Forgive me if this is very basic, but how do I paste the information without a link, all I

am doing it using the copy/paste commands within Excel and Word.That shouldn't be pasting with a link, unless some kind of macro is changing the default
behavior.

1. If you press Alt+F9, and the Excel table was pasted with a link, you should see a field
code in place of the table?

2. If you go over Paste Special, choose HTML or RTF and make sure Link is not activated, do
you get any different result?

I'd also like to double-check what I understood from your previous message:

I then try and do a mail merge in word to this data, and get the word error messages
about missing cell data (ie one record may not have an entry in address line 4, but others
do), and also I get all schemes, rather than just scheme 1, and also the reformatting to
'title case' is reverted back to the original case.

You are linking the mail merge to the document containing the Word table, right?

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply in the
newsgroup and not by e-mail

 

[mark Gray]

Cindy/Newsgroup

Thanks again for your response.

I tried the 'press Alt+F9' option and nothing happenned.

I also tried the paste special command, first of all with the HML option, and unfortunately, this includes all the unfiltered information (i.e all of the data on the Excel spreadsheet, rather than just the selected items) and also any reformatting of the data in the word sheet is lost when attempting to run the mail merge.

I then tried the paste special command with the RTF conversion, and this appears to do the trick at first glance. Only the selected information from the spreadsheet is included in the mail merge, and all the reformatting is carried through. However, where I have blank spaces in my data fields on Excel, these are carried through to the mail merge and are included under the wrong headings once merged (i.e. if address line 5 is blank, the next data item "statement type" is printed where the address line 5 should be on the spreadsheet, and as I have a lot of mail merge items to insert for a lot of different records this is a problem. And I still get the 'Record 1 contained too few data fields' warning which I am trying to suppress, this message appears whever the blank data cells exist so I could easily have over 100 of these warnings to click to ignore.

As a solution I could ensure that all blank fields on the spreadsheet are filled with a space, but this seems a little over the top, considering I can complete this task on a windows 98 machine without any problems whatsoever.

As a confirmation of the process I am doing, I am copying the filtered data from the spreadsheet to a blank word sheet. I am then reformatting the data so that all the case is correct. I then save this sheet (as say "data merge.doc"), and then open the word letter which requires the data inserting and link the letter to the "data merge.doc" and then attempt the mail merge.

If I prepare the "data merge.doc" on the windows 98 machine, and then run the mail merge on a windows 2000 machine I do not get any problems, but am concerned that the windows 98 machine may soon be disposed of, and then mail merges will become a major problem. If I attempt to amend any of the information contained on the "data merge.doc" on the windows 2000 machine I then get the 'Record 1 contained too few data fields' warning again.

I trust this is clear, and look foward to receiving further solutions.

Regards