I am using ODS tagsets.excelxp for the first time. I am just testing the code and writing the output to an .xls file on my desktop. The code runs fine on SAS; no errors. I can see the excel doc on the desktop with the .xls extension.
However, when I try to open the excel doc I get an error message saying "The file you are trying to open is in a different format than specified by the file extension". The file exists on the desktop and has the .xls extension. Does anyone have any idea why I am getting this error message?
Technically, when you call an XML file (what TAGSETS.EXCELXP creates) with a .XLS extension, that error message is correct, the file you are trying to open, '[filename]', is in a different format than the one specified by the file extension.
You just have to respond YES to the popup warning. Or else name the file .XML and do a File --> Open from Excel instead of launching Excel with a double click. http://support.sas.com/kb/35/581.html
Thank you, Cynthia.
I clicked "Yes" and tried to open the excel doc and was prompted "Unable to Read File". Then I changed the file extension to .xml and reran the code. When I opend the xml doc this is what's in it:
- X000000 X000000 2010-08-03T09:38:15 2010-08-03T09:38:15 SAS Institute Inc. http://www.sas.com 9.01.01M3P07282004
At this point, I would recommend that you work with Tech Support.
In all instances of running TAGSETS.EXCELXP and using Excel 2007 to open the file I am able to have Excel open the file when I click YES in response to that message -- using your code. When Excel opens, I have 6 worksheets, one for each AGE.
The XML that you see in the file is CORRECT -- TAGSETS.EXCELXP creates Spreadsheet Markup Language XML as defined by Microsoft for Office 2003. Excel 2007 should open the TAGSETS.EXCELXP XML file (even with a .XML extension). Did you open the file in the browser, in Notepad or with a lower version of Excel (such as Excel 2000)???
Usually, the only time I have issues with the Excel and the XML created by TAGSETS.EXCELXP is one of these circumstances:
1) try to name the file .XLSX
2) perform something in the SAS code that causes the XML to be invalid (like using embedded_title with a version of the tagset that didn't support embedded_titles) -- which doesn't seem to be the case in your code
3) Use EG and try to open the XML file with EG -- the file is OK -- but Enterprise Guide only "wants' to open SASReport XML -- not Spreadsheet XML
4) Use SAS 9.1.3 and have an older version of the tagset template (very similar to #2)
5) Use Office 97 or Office 2000 and try to open the file (since Spreadsheet XML was designed for Office 2002/2003, Excel 97 and 2000 will not open the XML file into a spreadsheet.