More on Mail Merge with Word and Excel, and FileMaker importing

Posted by Pierre Igot in: Microsoft
October 20th, 2004 • 4:39 am

Yesterday, I talked about the dreadful interface in Word for creating a form letter with data merged from an Excel spreadsheet. But it was only the first part of the story. Today, in the space of 15 minutes, once again, I had the opportunity to experience many of the things that are simply wrong with today’s software titles from major vendors.

After putting together the mail merge package (form letter in Word document with data in Excel document), and making sure that both files were by themselves inside a folder, right next to each other, I compressed the folder in question using Panther’s “Create Archive” command and sent the resulting Zip archive to the secretary who had asked for help.

I made sure that I gave her instructions preparing her for Word’s interface, and in particular I told her that, upon opening the form letter in Word, she would be greeted by a cryptic dialog box asking her to select the sheet in the Excel document containing the data for the mail merge.

I half suspected that this wouldn’t be enough and, sure enough, I soon got a phone call asking for help. She told me that, when she tried to open the form letter, Word told her that it could find the data and asked her to find it! At this stage, I knew that I had no choice but to go there and do the work on her computer myself.

I am not sure what happened here, because I tested this myself later on (sending the Zip archive to myself as an attachment, and then trying to open it and see if the connection between the form letter and data was preserved), and it worked. What I suspect happened is the following: in spite of my precautions, when the secretary was greeted with the dialog asking her to select the sheet (a step that shouldn’t even exist), she wasn’t sure what to do and probably clicked on “Cancel” instead of “OK“. If you do that (i.e. click on “Cancel” when Word asks you to select the sheet containing the data for the mail merge), Word gives you an alert saying:

“XXX” is a data merge main document. Word cannot find its data source, “YYYY”.

with three buttons: “Find File“, “Options“, and “Cancel“.

In other words, simply because the user said “Cancel” in a dialog box imposed by Word for a superfluous step that isn’t even necessary (selecting the sheet in the Excel document — when the document only contains one sheet with data), all of a sudden Word “cannot find its data source“. Right.

I am not sure that’s exactly what happened, but it’s possible. The thing is, when I got to the office and tried to do it myself on her machine, first saving the Zip archive somewhere on her hard drive and then expanding it and double-clicking on the Word form letter, Word greeted me with the same “cannot find its data source” dialog box right away!

Of course, I simply located the Excel file and selected it as the data source again. Once again, after that, Excel asked me to select the sheet (eyes rolling here), and finally it looked like we were in business. Except that… when I tried to use the “Data Merge Manager” palette that Word opened automatically when I opened the document, none of the controls would work! No matter how hard I clicked on them, nothing happened. I tried closing and reopening the palette, and then instead of the actual palette with the button I just got an empty windoid with nothing in it.

At this stage I just sighed, quit Word and relaunched it. I opened the form letter again, selected the sheet (eyes rolling again), and this time the palette behaved properly.

All these problems were just “glitches”, of course. It took me the whole of five minutes to work through them and get the thing working. But it’s because I am an experienced Mac user, and I am more than familiar with the many quirks and bugs in Microsoft’s Mac software. There is simply no way that the secretary could be expected to understand all this and know: (1) that the link between the form letter and the data source can easily be broken and might need to be restored by reselecting the data file; (2) that the dialog about selecting a sheet is utterly confusing but is a required step, even though it’s totally unnecessary; (3) that sometimes Word’s palettes simply don’t work and you have to quit and relaunch Word to get them working again. This secretary is just one of these alleged millions of Word users that, according to Microsoft, only use Word as a “fancy typewriter”. Have you ever encountered a fancy typewriter with so many quirks and bugs? I don’t think so.

Since I was there, I asked her how she was going to mail these letters (approximately 150 in total). She already has a FileMaker-based tool that I developed for her with FileMaker Developer 6 and allows her to print sheets of Avery labels with addresses. So I asked her if she wanted me to import the Excel data into the FileMaker tool, so that she could print mailing labels for these 150 letters. Needless to say, she was very grateful.

So I went to my FileMaker-based tool (a stand-alone address database created with FileMaker Developer) and tried to import the Excel data sheet into the database. First of all, when I selected the “Import” command, and navigated to the folder containing the Excel document, I noticed that the Excel document was greyed out and FileMaker wouldn’t let me select it as a data source. I was only half surprised: the Excel document had been created by the secretary and, since she’s a long-time Mac user, she’s not used to using file extensions for her files. So her Excel document didn’t have an “.xls” extension.

You’d think that FileMaker, a product developed and marketed by a full-owned Apple subsidiary that has been around since the early days of the classic Mac OS, would recognize Excel files based on creator/file type data and not just on file name extensions. Well, think again. As soon as I added the “.xls” extension to the file, FileMaker recognized it as a proper source of data to be imported.

For some reason, however, the FileMaker-based tool gave me the following error message:

“XXX.xls” could not be opened. (Error )

(Complete with the trailing space after “Error“). Now, that’s what I call a helpful error message!

What kind of error? I have no idea. Later on, I tried to import the Excel data into a database in FileMaker Developer 6 itself, and it worked just fine. Maybe it’s a limitation of stand-alone FileMaker-based solutions created with FileMaker Developer 6: They cannot import data from an Excel document. But at least you’d expect the error message to say so! You know, something like: “Sorry, this solution cannot import data from an Excel spreadsheet. Please import from a tab-delimited text file instead.” How hard would that be?

Since I am fairly experienced, I half-suspected that this was what the problem was (that the solution couldn’t import from an Excel document, but would be able to import from a tab-delimited text file). So I just opened the spreadsheet in Excel and saved it as a tab-delimited text file with the “.txt” extension. (You never know.) And finally I was able to import that into the FileMaker-based solution.

The bottom-line, once again, is that there is simply no way that anyone but a fairly experienced troubleshooter would have been able to complete these simple, seemingly straightforward office tasks. And that’s very wrong.

Why does Word have to ask to select the data sheet each and every time you open the form letter? Why did the connection between the Word form letter and the Excel data source break? Why did the “Data Merge Manager” fail to work properly until I quit and relaunched Word? Why does FileMaker require file extensions to identify Excel files as proper sources for data import? Why is a FileMaker-based solution unable to import data from an Excel file when FileMaker itself is able to do so?

These are all questions that I shouldn’t have to ask and the secretary shouldn’t have had to deal with. And that’s not even counting the completely useless and misleading wording of most alert messages in Office and FileMaker when something goes wrong.

As an experienced troubleshooter, I was able to get everything to work properly in less than 15 minutes. But isn’t this the kind of task that office workers should be able to accomplish without having to call an experienced troubleshooter?

2 Responses to “More on Mail Merge with Word and Excel, and FileMaker importing”

  1. Dan Farrell Davis says:


    Boy, I had forgotten how much group therapy you provide here since I’ve been an itinerant reader for the last few months. MS Office is so frustrating software, isn’t it?

    Just hearing your travails here made me feel better since (a la group therapy) it was not me suffering the same idiocy. The only positive thing is that this kind of crap keeps us employed.

    Positive thinking made be hope that Office 2004 would fix some stuff in Data Merge Manager and it file location problems, inserting text boxes and formatting them, and generating “catalogs” using data merge.

    Perhaps you’ve already written about the fact that formatting text boxes doesn’t seem to work from the Formatting Pallette but does from the Format Text Boxes menu item. If not, be warned.

    Anyway, Pierre, I respect your ability to mush on through the problems so articulately.

  2. Pierre Igot says:

    Thanks :-). I personally avoid text boxes like the plague, but I realize that there are situations where they are unavoidable. One of the reasons I avoid them (only one of them) is precisely that formatting/editing them is so painful. I am not surprised to hear that the Formatting Palette doesn’t help with this at all. When you look at the way that Word’s commands are named in the “Customize” environment, it’s obvious that Microsoft engineers have a way of organizing things that defies comprehension. So expecting them to make such formatting tools accessible through the Formatting Palette is definitely too much to ask. After all, we wouldn’t want things to be too easy, would we? As you said, it keeps us employed (and Bill Gates very rich).

Leave a Reply

Comments are closed.