MATLAB Central Newsreader - xlswrite to Excel 2007 problem

xlswrite to Excel 2007 problem

Michael Lenz — Thu, 04 Sep 2008 17:08:02 -0400

Hello All -

I've got a function that utilizes xlswrite to spit out some
summary data to xls sheets for quick review; a function
that I've been using for several months. Recently, I
installed Office 2007 and am now running into problems with
people running Excel 2003 not being able to open the xls
files I've written.

I've made no changes to the function since I last used
Excel 2003 and the output files are being saved with
the .xls extension. The files open fine with Excel 2007,
but when I try with Excel 2003 I get a warning message that
says the file is in an unrecognizable format. When I
proceed to open the file, it is a mess of miscellaneous
characters.

Any ideas or suggestions?

Thanks!

Re: xlswrite to Excel 2007 problem

otto — Thu, 04 Sep 2008 18:29:19 -0400

Go back to Excel 2003! I've been resisting the upgrade for years.

Not sure right now - I haven't used xlswrite in a number of years, but
always remember that it was finicky when I did. Sometimes when
Outlook checked email it would break the connection between Matlab and
Excel. I think your problem is due to the way Matlab interfaces with
Excel in xlswrite.m in that it starts a COM connection with whatever
version of Excel that's on your machine and generates the output file
by sending commands to Excel through the COM interface. So even
though you don't directly see the XLS file you're generating it with
your incompatible version of Excel.

I would suggest looking into the compatibility options in your version
of Excel (probably really deep in there) to see if there's a way to
always save in a backwards compatible format or run extra checks
everytime it saves a file. If that fails you you can always write the
data to a CSV file for quick sharing if people don't have a lot of
hangups with format... The CSV will open with the least complications
in any version of Excel.

Cheers,
Otto

On Sep 4, 1:08=A0pm, "Michael Lenz" <mcl...@sandia.gov> wrote:
> Hello All -
>
> I've got a function that utilizes xlswrite to spit out some
> summary data to xls sheets for quick review; a function
> that I've been using for several months. =A0Recently, I
> installed Office 2007 and am now running into problems with
> people running Excel 2003 not being able to open the xls
> files I've written.
>
> I've made no changes to the function since I last used
> Excel 2003 and the output files are being saved with
> the .xls extension. The files open fine with Excel 2007,
> but when I try with Excel 2003 I get a warning message that
> says the file is in an unrecognizable format. =A0When I
> proceed to open the file, it is a mess of miscellaneous
> characters.
>
> Any ideas or suggestions?
>
> Thanks!

Re: xlswrite to Excel 2007 problem

Michael Lenz — Thu, 04 Sep 2008 18:32:03 -0400

A little more detail:

I think I can get around the problem if I can get xlswrite
to invoke Excel 2003 rather than 2007. To do this I need
the programmatic identifier (progid) to feed to the command
Excel = actxserver('Excel.Application'). The trouble is
that, from what I've uncovered is that both Excel 2003 and
2007 use Excel.Application as their progid. Does anyone
happen to know how I can change the progid of Excel 2003 to
see if that works? Perhaps I can get 2003 to be invoked if
I knew what the interface name for Excel 2003 (from Office
11) is.

Thanks.
M Lenz

Re: xlswrite to Excel 2007 problem

Bill August — Thu, 04 Sep 2008 18:42:13 -0400

> Hello All -
>
> I've got a function that utilizes xlswrite to spit
> out some
> summary data to xls sheets for quick review; a
> function
> that I've been using for several months. Recently, I
>
> installed Office 2007 and am now running into
> problems with
> people running Excel 2003 not being able to open the
> xls
> files I've written.
>
> I've made no changes to the function since I last
> used
> Excel 2003 and the output files are being saved with
> the .xls extension. The files open fine with Excel
> 2007,
> but when I try with Excel 2003 I get a warning
> message that
> says the file is in an unrecognizable format. When I
>
> proceed to open the file, it is a mess of
> miscellaneous
> characters.
>
> Any ideas or suggestions?
>
> Thanks!
>
Hi,
xlwrite do not support customize format. So although you give the file extension as '.xls'. IT will still use 2007 format.
One way without modify xlswrite is, run these lines first:
Excel = actxserver('excel.application');
set(Excel, 'DefaultSaveFormat', 'xlExcel9795') ;
Excel.Quit ;
Otherwise you have to modify the xlswrite.
Regards.

Re: xlswrite to Excel 2007 problem

Michael Lenz — Thu, 04 Sep 2008 19:11:02 -0400

> Hi,
> xlwrite do not support customize format. So although you
give the file extension as '.xls'. IT will still use 2007
format.
> One way without modify xlswrite is, run these lines first:
> Excel = actxserver('excel.application');
> set(Excel, 'DefaultSaveFormat', 'xlExcel9795') ;
> Excel.Quit ;
> Otherwise you have to modify the xlswrite.
> Regards.

Thanks Bill. I've tried this and get the following error:
???Error: Object returned error code: 0x800A03EC

that looks like some kind of Excel limitation. Even using
the inspector window in MATLAB, if I try changing to
xlExcel9795, the change won't take effect.

Re: xlswrite to Excel 2007 problem

Michael Lenz — Thu, 04 Sep 2008 19:37:01 -0400

Okay, here's something else...

It has to be a save format issue. I right-click the file
spat out by xlswrite to do Save As and Windows insists that
the file is a .xlsx file. If I open and Save As a .xls with
Excel 2007, the file opens just fine in Excel 2003.

Re: xlswrite to Excel 2007 problem

Bill August — Thu, 04 Sep 2008 21:09:12 -0400

> A little more detail:
>
> I think I can get around the problem if I can get
> xlswrite
> to invoke Excel 2003 rather than 2007. To do this I
> need
> the programmatic identifier (progid) to feed to the
> command
> Excel = actxserver('Excel.Application'). The trouble
> is
> that, from what I've uncovered is that both Excel
> 2003 and
> 2007 use Excel.Application as their progid. Does
> anyone
> happen to know how I can change the progid of Excel
> 2003 to
> see if that works? Perhaps I can get 2003 to be
> invoked if
> I knew what the interface name for Excel 2003 (from
> Office
> 11) is.
>
> Thanks.
> M Lenz
Hi
It is quite easy to solve.
Just some different between 03 and 07.
Try this,
set(Excel, 'DefaultSaveFormat', 'xlExcel8')
Regards.
Also you can set it in Excel 2007. It is in file->excel option->save option card.
Hope this can help.

Re: xlswrite to Excel 2007 problem

Michael Lenz — Thu, 04 Sep 2008 22:57:02 -0400

> Hi
> It is quite easy to solve.
> Just some different between 03 and 07.
> Try this,
> set(Excel, 'DefaultSaveFormat', 'xlExcel8')
> Regards.
> Also you can set it in Excel 2007. It is in file->excel
option->save option card.
> Hope this can help.

Bill, thanks for the suggestions. I tried changing to
xlExcel8 in the inspector window to no avail. I'll give it
another shot using the set command to see what happens.

I have noticed that even when I open the files with Excel
2007 I'm asked if I want to continue because the file is a
different format than the .xls extension.

Silly problem I know, but it is driving me crazy because it
should have a simple solution.

Thanks again!

Re: xlswrite to Excel 2007 problem

Philosophaie — Fri, 05 Sep 2008 01:46:02 -0400

You have to initialize the Workbook and Worksheet

function ExcelFn
exl = actxserver('excel.application');
exlWkbk = exl.Workbooks;
exlFile = exlWkbk.Open
(['C:\folder\file']);
exlSheet1 = exlFile.Sheets.Item('Sheet1');
input=exlSheet1.Range('B3');

...Matlab formulas...

exlSheet1.Range('C3')=output;
exlWkbk.Close
exl.Quit
end

Re: xlswrite to Excel 2007 problem

Michael Lenz — Fri, 05 Sep 2008 17:46:03 -0400

Thanks, but xlswrite does initialize the workbook, worksheet and other tasks necessary to write to Excel.

I have determined that regardless of what DefaultSaveFormat is set to, the result of running xlswrite is a file in the .xlsx format. Thus far, the only solution is to go back, open the new file with Excel 2007 and using Save As to make the file into a .xls by selecting 97-2003 format.

In Excel 2007 I have gone to Excel Options>>Save>>Save files in this format: and select Excel 97-2003 Workbook (.xls) prior to running xlswrite; this doesn't work either. The funny thing is that if I open Excel 2007, make a workbook and save it (with the Excel save option selected above), it opens just fine in 2003.

It seems as if xlswrite uses the default save format of the latest version of Excel (.xlsx), regardless of what I set DefaultSaveFormat to be with MATLAB.

My next guess is to open and save a 'template', empty, workbook in Excel 2003 and modify xlswrite to go open this template.xls file, add my data, then save with a new file name. I have some PPT functions that do this and they work just fine regardless of the version of Office.

Anyway, this has been driving me crazy, so I wanted to provide an update for anyone else that might encounter version issues with Excel.

The ridiculous part is that if Excel 2003 and 2007 had different progid names to pass to the actxserver(progid) function, this whole issue could be avoided. Unfortunately, both 2003 and 2007 have the progid 'Excel' and I haven't found a way to change that.

M LENZ

Re: xlswrite to Excel 2007 problem

Michael Lenz — Mon, 08 Sep 2008 03:54:01 -0400

Final note:

I finally had to resort to a small modification to xlswrite. I opened a blank workbook with Excel 2003, then saved it as "XLTemplateFile.xls". The modification to xlswrite now opens this template file, then sets up the workbook, and saves the .xls template using the input file name - just like xlswrite. Here is the small mod I made, just as an FYI (starts at line 207 in xlswrite):

try
    bCreated = false;
% if ~exist(file,'file')
% % Create new workbook.
% bCreated = true;
        %This is in place because in the presence of a Google Desktop
        %Search installation, calling Add, and then SaveAs after adding data,
        %to create a new Excel file, will leave an Excel process hanging.
        %This workaround prevents it from happening, by creating a blank file,
        %and saving it. It can then be opened with Open.
% ExcelWorkbook = Excel.workbooks.Add;
% ExcelWorkbook.SaveAs(file)
% ExcelWorkbook.Close(false);
% end

    %Open file
    ExcelWorkbook = Excel.workbooks.Open(XLTemplateFile);
    ExcelWorkbook = Excel.workbooks.Add;
    ExcelWorkbook.SaveAs(file)
    bCreated = true;
    ExcelWorkbook.Close(false);
    ExcelWorkbook = Excel.workbooks.Open(file);

Anyway, just wanted to pass that along in the event that anyone else runs into the same issue that I did.

Thanks to everyone that posted suggestions on this thread.

M Lenz

Re: xlswrite to Excel 2007 problem

Dan Haeg — Thu, 06 Nov 2008 22:02:02 -0500

"Michael Lenz" <mclenz@sandia.gov> wrote in message <ga27kp$e4l$1@fred.mathworks.com>...
> Final note:
>
> I finally had to resort to a small modification to xlswrite. I opened a blank workbook with Excel 2003, then saved it as "XLTemplateFile.xls". The modification to xlswrite now opens this template file, then sets up the workbook, and saves the .xls template using the input file name - just like xlswrite. Here is the small mod I made, just as an FYI (starts at line 207 in xlswrite):
>
> try
> bCreated = false;
> % if ~exist(file,'file')
> % % Create new workbook.
> % bCreated = true;
> %This is in place because in the presence of a Google Desktop
> %Search installation, calling Add, and then SaveAs after adding data,
> %to create a new Excel file, will leave an Excel process hanging.
> %This workaround prevents it from happening, by creating a blank file,
> %and saving it. It can then be opened with Open.
> % ExcelWorkbook = Excel.workbooks.Add;
> % ExcelWorkbook.SaveAs(file)
> % ExcelWorkbook.Close(false);
> % end
>
> %Open file
> ExcelWorkbook = Excel.workbooks.Open(XLTemplateFile);
> ExcelWorkbook = Excel.workbooks.Add;
> ExcelWorkbook.SaveAs(file)
> bCreated = true;
> ExcelWorkbook.Close(false);
> ExcelWorkbook = Excel.workbooks.Open(file);
>
> Anyway, just wanted to pass that along in the event that anyone else runs into the same issue that I did.
>
> Thanks to everyone that posted suggestions on this thread.
>
> M Lenz

I had a simmilar problem with xlswrite. This seems to work outside of xlswrite:
ExcelWorkbook.SaveAs(file,39)

39 saves it to excel95 format
-4143 saves it to excel's default format, which i used to convert csv files to xls silently

here is a list of possible values I found:
http://www.pcreview.co.uk/forums/thread-3042211.php

explained poorly here:
http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.xlfileformat(VS.80).aspx

Dan

Re: xlswrite to Excel 2007 problem

Shai Carmi — Mon, 21 Dec 2009 13:18:04 -0500

Thank you very much. This is working for me- I simply replaced line 218 of the file xlswrite.m with:
ExcelWorkbook.SaveAs(file,39)
and I could read the saved Excel files in Excel 2007 without warning messages. The file was also read in Macintosh without any problem.