Word 2002 Mail Merge Error via Excel 2002, XP Op System

[Techy Wannabe]

This only happens on one file. I received a download of data in an
excel file (same version I run, same op system I run) and saved the
original file and then saved a copy of the file under a new name and
sorted the data, split the data between 3 worksheets based on specific
criteria, applied general formating (col width, row height, color,
etc.).

I use the merge wizard in Word 2002, which I have done hundreds of
times. But this document is giving me intermittent problems. I follow
the below method.

Document Type: Letter
Start Document: Use the current doc
Select recipients: Browse
I select the copy of the original file
Select Table: I choose Sheet1$ from the following options: additional$,
approved$, dvo7lkup$, rejected$)

When the mail merge recipient window opens it is not populated with
Sheet1$ information it has sheet additional1$

I X out and go back to:

Select a different List
I select the same file (copy of the original file)
Now the Select Table has the following options: Sheet1$, Sheet2$,
Sheet3$, and so on.
I select Sheet1$ (I have selected them all in hopes of troubleshooting)
and the mail merge recipient window is blank.

If I close out and start over it is correct. But if I decide that I
need to tweak the data more and cancel the merge and come back later
the same thing happens again.

I tested the original doc and I do not get the errors. I checked to see
if there was code b/c there shouldn't be on a simple text doc. and
there isn't any code. the VBA is the same on the original as it is on
the copy. The original was sent from the same version of windows and op
system.

I cannot figure this out. I know just use the original but what if this
occurs again.

I would appreciate any insight anyone can give me. Thank you so much!

 

[Peter Jamieson]

Some thoughts:
a. could the workbook you are receiving be corrupt? You could experiment by
exporting its contents to .csv then importing to a fresh .xls create don
your system, then proceed as you are already doing.
b. is the .xls closed when you try to connect to it?
c. do you disconnect the existing data source before connecting (even if it
is the same workbook)? (e.g. use the MailMerge toolbar button 1 to set the
document to "Normal Word document", which should do the disconnect. Then
select the merge type you want and proceed with the connect.
d.
<<
Select Table: I choose Sheet1$ from the following options: additional$,
approved$, dvo7lkup$, rejected$)
Is there a "Sheet1"? Or do you choose sheet "additional" ? or what?

(That's about as far as I can follow for now...)

Peter Jamieson

 

[Techy Wannabe]

Hi there and Thank you! Sorry for the delayed response got derailed
for awhile.

OK, I followed your plan and have added the alpha characters to
represent the character designators in the 1st response.

a. Saved the original as a .csv and started from scratch. It was
tedious because you can only convert one sheet and not an entire
workbook. Anyway, it seems to have worked. I am unable to recreate the
error.

b. The .xls was closed when I made my initial inquiry. This time it
was open and there were no problems. Should the .xls be closed before
a merge? Sometimes I close the database and sometimes I don't.

c. I believe the data source was disconnected. Strange thing though, I
would close all instances of excel and word. When I checked the task
manager/processes it showed an instance of excel.exe still running. So
I would end the process and start again. I checked the event viewer
and there are no errors or warnings.

d. Yes, there is data in sheet1$ as well as additional$, approved$,
dvo7lkup$, rejected$. It didn't matter which sheet I chose the data
would be incorrect or absent.

Is there anyway to determine what corrupted the file?

Thanks again!

 

[Peter Jamieson]

<<
b. The .xls was closed when I made my initial inquiry. This time it
was open and there were no problems. Should the .xls be closed before
a merge? Sometimes I close the database and sometimes I don't.
1. There can certainly be locking problems if you try to have the worksheet
and the mail merge main docment open at the same time, but I couldn't spell
out the exact circumstances in which you will see messages. But for example,
if you open your mail merge main document, connected to the worksheet,
first, then try to open your worksheet in Excel, you will probably see an
"open rad-only?" dialog.

c. I believe the data source was disconnected. Strange thing though, I
would close all instances of excel and word. When I checked the task
manager/processes it showed an instance of excel.exe still running. So
I would end the process and start again. I checked the event viewer
and there are no errors or warnings.

2. It's possible that Word has tried to connect using DDE, got as far as
starting Excel to do so, but got no further and left an invisible instance
of Excel lying around. I don't know for sure, but I've seen similar stuff
happen here.

Is there anyway to determine what corrupted the file?

3. I don't know, but it occurs to me that if (2) does happen, it might be
enough to cause problems. But that really is pure conjecture on my part.

Peter Jamieson