Suppress opening the .XLS file created with ODS

[deleted_user]

Greetings:

Since MS changed to v7, we have this problem with an MS error box that opens when the .xls file is created. I can't get rid of it. This is the text of the message:

"The file you are trying to open, ~.xls, is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening this file. Do you want to open the file now?"

Then of course, you need to click Yes or No. I do not want user interaction for this code. I have been looking for options to suppress SAS/ODS from trying to open this file. Perhaps I need to look at MS Excel not to present the error box? Anyone else have this problem?

Thanks,
Francine Cannarozzi
College of American Pathologists
Northfield IL

[Cynthia_sas]

Hi:
This is a MICROSOFT warning.. Technically, the warning message is correct when it says:
The file you are trying to open '******.xls' is in a different format than specified by the file extension.

The reason your are getting that message is that even if you name the file with a .XLS extension, when you use ODS, you are not creating a "true, binary" Excel file. You are merely creating an ASCII text XML (Spreadsheet Markup Language Excel 2003) file that Excel knows how to open and render. Naming the file .XLS will fool the Windows Registry into launching Excel when the file name is clicked. So, that's why the warning is technically correct -- the .XLS extension tells Excel that you are giving it a "binary Excel 97-Excel2000" format file and when Excel goes to open the file -- ta da! -- it discovers XML tags.

This is a normal "security" message from Office 2007, and you can just click YES to open the file. Or, as the Tech Support note suggest, just name the file with .XML instead of .XLS and you should not get the message.
http://support.sas.com/kb/31/956.html

Although the downside of this approach is that your users may not want to receive files with an extension of .XML, even if this extension does reflect the true nature of what is inside the file. (Look inside the created file with Notepad -- and you will see the XML tags.) Also, if you are mailing the files, some mail applications treat HTML and XML files as "dangerous" files and they might not let the mail go through.

There is nothing that SAS can do to prevent this Excel message from popping up. Your possible solutions are to:
1) educate the users that it's OK to click YES and tell them if they want to save the file as XLS (they have to do a Save AS, not just an automatic SAVE)-- then the next time they open the file, they won't get the message.

2) educate the users about using File--> Open to open a file with the extension of .XML (and stop putting .XLS as the file extension in your ODS step)

3) Make the registry change that Microsoft recommends, as shown in the Tech Support note (but you would have to make the registry change on EVERY machine)

4) write the XML files from ODS in a directory and have someone write you a VB or VBA program to go out to that directory, open the XML files and do a SAVE--AS to a binary XLS format

5) if you do not care about the colors, fonts and formatting in the XML file created by ODS, then, switch to the LIBNAME engine or to PROC EXPORT to export to "true, binary" Excel files from SAS datasets (usually needs SAS/Access to PC File Formats)

cynthia