After mail merge, "File format is not valid" error when opening XLS file

[Bob on Whidbey]

While completing a mail merge in Word, I edited a few fields in the
data base (an XLS file). Upon closing Word, I was asked if I wanted to
save the changes to the data base. Of course I said yes. All
subsequent attempts to open that XLS file have been blocked with the
error message in Excel: "File format is not valid". I am using Office
2003.

It seems like Word has created a different version of the file for its
purposes, and overwritten the XLS file in the process. Yet, it's hard
to believe that Word would create a different version of the file and
keep the same file extension!

What do I need to do to open that XLS file in Excel?

 

[Peter Jamieson]

I would be interested to know how you are editing your Excel data source.

If I select an Excel worksheet as a data source in Word 2003 using the
default connection method (OLE DB), and use "Edit "Mail Merge Recipients",
the Edit button is greyed out. If you are unaware of what connection method
you are using, then Word should be using OLE DB.

At least one reason why Microsoft probably disabled the Edit button in this
case is because the mechanism that Word would have to use to edit the data
when it connects using OLE DB is unreliable and likely to cause exactly the
kind of corruption you describe.

If you have checked Word Tools|Options|"Confirm conversion at Open" and
selected DDE, then the worksheet should be opened in Excel and clicking that
Edit button in Word should take you to Excel to edit the data. Another
possibility is that Word.

If you used (or Word used) ODBC to make the connection, Word enables the
Edit button, but when you click it, Word asks if you want to use MS Query to
edit it. If you say no, nothing happens (AFAIK) - i.e. you get no
opportunity to do the edits. If you say Yes, in theory Word opens MS Query,
but again, it now uses an editing mechanism that I suspect is now
unreliable.

Maybe your copy of Word is doing something different?

As for what you can do to recover your existing data, I'm sorry, I simply
don't know Excel well enough to make a suggestion - you might try an Excel
group. Otherwise, let's hope you have a recent backup.

Peter Jamieson

 

[Bob on Whidbey]

I created the data file (DB) with MS Excel. Then I used Word and a
mail merge. While in mail merge I edited the DB. Access to the editor
was on the Mail Merge tool bar, where it says "Edit recipient
list..."

Upon selecting to edit the DB file, I am not asked about using MS
Query. When I click the "Edit recipient list..." my data appears in a
box, ready for selecting records and then making edits.

While closing Word, I was asked if I wanted to save my changes to the
DB and I clicked yes. The file still has an XLS extension, but can no
longer be opened with Excel. However, I can get into Word's mail merge
and still edit any field I want. Or course, no formulas can be used.
So the file is definitely not corrupted - at least as far as mail
merge is concerned. My problem is - I want to be able to edit the file
in Excel. Most importantly I don't want to lose that file. At the
moment, it can only be used with mail merge.

When I first open the DOC file, I am warned "Opening this document
will run the following SQL command
SELECT * FROM C:\....name of my file"

Whether or not I check yes or no seems to make little difference. If I
click no, later on I can select the appropriate XLS file - with no
warning.

I'm guessing that Word is using an OLD DB provider for ODBC Drivers.

"Confirm conversion at Open" is NOT checked. When I edit a field in
Word, the "View Source" button is the only button grayed out.

Yes, I do have pretty good back up. I'm trying to discover
1. what format the XLS file is actually using.
2. how did I possibly convert my file from an Excel format to this new
format (whatever it is), so I can avoid doing that in the future.
3. is it really true that Word could have converted my Excel file to a
new format - but still use the same file extension.
4. since Word somehow converted this file to its own format, is there
a way I can convert it back to Excel's format.

 

[Peter Jamieson]

OK, the situation is slightly different from what I imagined, in that you
describe opening an existing .doc.

In that case there is one other way you could have made the original
connection, and that's to use the "Excel Converter", which is a Word
external text converter that knows how to read .xls files. IN that case, you
would see the .xls pathname in the SQL security message that pops up when
you open the .doc, rather than (for example) 'Sheet1', and the Edit button
would be available in the "Edit Recipients" dialog box, and you would be led
to a simple editing form if you clicked it.

However, the problem with the Excel converter is that it was last actually
supplied in Office 2000 or 2002, and is no longer provided even with the
"Converter pack" you can download from the MS site. In other words, it's no
longer supported. I'd forgotten that it will let you try and update Excel
data, but again, I think it highly unlikely that it would do that reliably
now. If you want to verify that, you could either do what I have done, which
is to try to use the Immediate Window in the WOrd VBA editor to print the
value of ActiveDocument.MailMerge.DataSource.ConnectString - here, it
crashes Word if the data source is the Converter - or you could try saving
your .doc as a .HTML file, the open the .HTM in Notepad, or Word (but as a
plain tect file) and have a look at the HTML/XML that specifies the
MailMerge connection.



So...

Yes, I do have pretty good back up.
Good.

Yes, I do have pretty good back up. I'm trying to discover
1. what format the XLS file is actually using.

My new guess is that it's some version written by the old converter, and it
may also not be recognisable by Excel itself.

2. how did I possibly convert my file from an Excel format to this new
format (whatever it is), so I can avoid doing that in the future.

One way of looking at it is "don't edit the Excel file from within Word
MailMerge". If you don't have to use the converer to get the data (and
sometimes there are reasons for doing it even though it's obsolete),
re-attach the data source using the current method (you can see
http://tips.pjmsn.me.uk/t0003.htm for a lengthy article about some of the
issues).

3. is it really true that Word could have converted my Excel file to a
new format - but still use the same file extension.

Yes. The Extension is just an extension. The association between a file
extension and the file content is not hardwired. For example, if you happen
to have WordPerfect as well as Word, it also uses the extension ".doc" for
word processing documents. Why wouldn't it - it has been around longer than
Word

4. since Word somehow converted this file to its own format, is there
a way I can convert it back to Excel's format.

I don't know, but I certainly wouldn't want to rely on being able to do
that. If I had to try, I would start writing some VB/VBA ADO code to try to
open the .xls and save it in the current .xls format, but I have no idea
whether or not it would work.

I'm guessing that Word is using an OLD DB provider for ODBC Drivers.

(FWIW I have never seen a situation in which Word actually uses that
provider - it may be possible to get it to do so if you use a .odc or .udl
file with an appropriate connection string if you happened to need to do so.
In the case of file types such as Excel, Word uses the Jet database engine,
and broadly speaking if the file can be accessed via the Jet ODBC driver, it
can also be accessed via the Jet OLE DB provider. There are sometimes
differences in the connection string parameters that can be used, the file
extension names that can be used, and where exactly the driver/provider gets
configuration info. from, )

Peter Jamieson

 

[Peter Jamieson]

4. since Word somehow converted this file to its own format, is there
a way I can convert it back to Excel's format.

I looked at this again and it turns out that Word is saving the data source
as a Word document. However, it is retaining the .xls extension. So what you
would probably need to do to recover the document is
a. open it in Word
b. copy/paste into Excel.

However, a lot of information is likely to be lost in that "round trip" from
Excel via Word to Excel again. For example, no formulas will get through, no
column formats, etc. etc.

Peter Jamieson