Mail Merge (Excel -> Word) and Sheet issues

[Kalechibki]

Hey all

I was using Mail Merge (Thanks Dreamboat! ^_^) to put values into a word document from an excel spreadsheet. However, I'm running into a problem with the merge itself. I've been succesful if I've copied all the data I need to merge from the main spreadsheet in a workbook to another workbook, with nothing else but the data to be merged. That's worked.

However - when I've tried to do it from the main spreadsheet, or moved it onto a seperate sheet, but still in the same workbook as the main spreadsheet, it's been giving me trouble, telling me usually one of two things. The first is that it couldn't make a DDE connection. The second is that when it goes to merge, that all the fields are empty! And this is when I've used the EXACT same data setup as I did in another workbook.

Any ideas on what I'm doing wrong? Am I putting in the Open Source incorrectly (if I'm doing the seperate sheet thing, I've been putting in 'filename.xls!sheet3').

 

[Dreamboat]

When you open the main doc, get the mail merge helper and hit open data source. Hit the option button. Make sure you're using DDE... Anne Troy

http://www.TheOfficeExperts.com

 

[Kalechibki]

Hey

I tried to find this "option" button (I'm working with Office 2000, btw), and couldn't find it.

What I then did was try messing with the Header Options, and now I have those set for the right columns. But, now when it tries to merge, even if when I try the open source document, and type "Sheet3" after choosing the file, it still seems to select entire document because now it's printing out a couple of columns from the sheet 1 of the same workbook.

Sorry for being such a pest, it just seems that everything I'm trying leads me to a dead end, and the help documentation for Word and Excel aren't helping. Thanks for all the help so far.

 

[Dreamboat]

The quickest solution I can provide is to put all records in one worksheet.

If your sheets are broken down by some variable, then add a column to your Sheet1 that contains that variable. Suppose you have sheets for January, February, March...

Then put them all into one worksheet, with a column for MONTH. And put the month in there.

Then, in your Mail merge, do a query that only gives you Jan, Feb, Mar...etc. Anne Troy

http://www.TheOfficeExperts.com

 

[AngO]

Kalechibki,

Dreamboat's first suggestion will work, You need one extra step for that option to show in the datasource window.

In word, go to Tools, Options. On the General tab, select the Confirm Conversion at Open check box.

After you complete this step the rest of Dreamboats instructions work great.

Good Luck.
AngO

 

[Dreamboat]

Hmm...no.
Confirm conversion has nothing to do with the way a mail merge doc connects to its data source.

Actually, open the main doc.
Then get the mail merge helper up.
Hit Data Source--Open data source.
At the bottom, there's a checkbox to Select Method.

THAT's where they need to go.

The confirm conversion is for like when opening a WordPerfect doc, it'll tell you it's converting instead of just converting. Anne Troy

http://www.TheOfficeExperts.com

 

[AngO]

The issue we were dealing with was different, but solution was still the same, needed to get to DDE. Without taking that first step, changing the data exchange was not an option on xp.

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q320473

 

[jmuscat]

Dreamboat is correct, when selecting the Select Method checkbox and the filename and file type i.e. xls, another window will display, from there select Microsoft Excel via Converter (xls, xlw) so that when merging you can select whichever sheet number you require.
JMuscat

 

[desireeg]

Kalechibki,
I have found that when doing mail merges with Excel data that if the data isn't on the first sheet, Word seems to run into problems finding the data (blank fields). The work around I use for this issue is to give the data to be used for the merge a Range Name. Then when I start the merge and go to specify a data source, I choose the workbook and then a list of the named ranges appears and I choose the correct range.

Hope this helps!
Desiree

 

[AngO]

Sorry - missed the notation about office 2000. For some reason I was thinking about XP!