topic Re: Creating Excel 2007 files using ODS on Linux in ODS and Base Reporting

Creating Excel 2007 files using ODS on Linux

SASHunter — Wed, 07 Apr 2010 15:17:53 GMT

I know your going to say - I should be able to find this on SAS's website, but I haven't quite found what I am looking for.

We are on the Linux machine and the version of SAS is 'SAS 9.2 TS Level 2MO

We want to create a Excel file (version 2007) on the Linux that we can ftp to the PC and open in Excel 2007. How do I do this is ODS?

The current code we use is like this:

ods noresults;
ods listing close;
ods html body="$DATA/&mmm&yy.summary_rpt.xls";

...

ods html close;
ods listing;

Can I change the extension on the summary_rpt.xls to 'summary_rpt.xlsx' and once I ftp to the PC open in Excel 2007?

Thanks, Nancy

Re: Creating Excel 2007 files using ODS on Linux

sbb — Wed, 07 Apr 2010 15:51:33 GMT

You can most definitely open an "XLS" type Excel document in Excel 2007 - the file does not need to have an "XLSX" extension.

Scott Barry
SBBWorks, Inc.

Suggested Google advanced search argument, this topic/post:

+ods +"excel 2007" site:sas.com

Re: Creating Excel 2007 files using ODS on Linux

SASHunter — Wed, 07 Apr 2010 16:10:43 GMT

Thanks for the reply.

I have done more reading since posting this. Now I understand that - just because the file has the extension (.XLS) does not mean that it created an Excel file. It created an ASCII file that I can open in Excel. In testing we changed the extension to (.XLSX) on the Linux side, the file could not be opened in Excel anymore. It gave us a warning, we clicked OK, but nothing opened.

So we put it back to (.XLS) and now staying with that. Once we get the file over to the PC side, I suggested writing code that would save the file as an (.XLSX) file.

Thanks again,
Nancy

Re: Creating Excel 2007 files using ODS on Linux

Cynthia_sas — Sat, 10 Apr 2010 14:07:55 GMT

Hi:
ODS HTML, ODS CSV and ODS TAGSETS.EXCELXP all create different types of ASCII text files (HTML, comma-separated and Spreadsheet Markup Language XML) that can be opened and rendered with Excel. The XML created by TAGSETS.EXCELXP is Office SpreadsheetML version 2002/2003.

The new .XLSX files are a different type of XML -- in fact, an XLSX file is not actually ONE file, it is an archive of files. If you perform the "experiment" described in this previous post:
http://support.sas.com/forums/thread.jspa?messageID=18646䣖
of renaming an XLSX file with the extension .ZIP, you will see the new structure.

Microsoft is very picky about which file extensions you use -- so naming an HTML file with a .XLSX extension is not going to make Microsoft happy to open the file. With Office 2007 in particular, even naming an HTML file with a .XLS extension or naming a 2003 XML file with a .XLSX extension causes Excel to complain upon opening the file.

Your approach of creating the HTML file and then having a program do a SAVE to the format you want is a good approach. Or you could educate your users ....

cynthia

Re: Creating Excel 2007 files using ODS on Linux

SASHunter — Mon, 12 Apr 2010 13:04:12 GMT

Thanks for your reply.

I have seen the .zip files inside .xls files. I have worked with the Ribbon in Excel 2007 to create new ribbons, so I have to modify the customUI file inside the .zip file.

Thanks so much.
Nancy

Each day I learn something new.