SAS 9.2 and Excel 2010 - change in formats or unreadable error

[Reeza]

What I'm trying to do:

I have an Excel report built with the data sources on a separate sheet that feeds pre formatted tables and graphs.  The template is saved as a .XLS file.

When I export data to it from SAS 9.2 using the libname engine (excel) then the formatting changes, e.g. colours and styles, some things go bold and others change colours. 

I tried saving the file as .xlsx and then writing to it that way, there are no more format changes but I get an error:

Excel found unreadable content" error in .xlsx file

I found this usage note on how to get rid of it but not sure it applies.

40383 - An unreadable content error occurs when you open Microsoft Excel files that were created by ...

Has anyone come across this issue before and is there a workaround/solution. 

There doesn't appear to be any actual unreadable content that I could find and the reports work fine except for the error.

[Domenico]

Hi Reeza,

i got this problem one week ago. I could resolve it by changing the statement option from dbms=xls to dbms=excelcs.

By the way keep .xls not .xlsx!!

best regards

Domenico

[Reeza]

ExcelCS doesn't allow me to drop my tables and/or rewrite over them though.

[Domenico]

Did you add the replace statement?

I'm using SAS 9.2 M3 and Windows7 Office2010 and the code below works for me very fine.

proc export data=work.datahave

    outfile="datahave.xls"

    dbms=excelcs replace;

    sheet= "Datahave";

run;

I forgot to mention that if you have already an existing file which you have exported with the statement option dbms=xls, then you have to use a different filename for the first time. Afterwards it shouldn't be a problem to replace the file.
Good luck

Domenico