Generate Excel file for Office 2010 from Mainframe

[NCNyrk]

For years, I have used ODS  HTML3 to create "fake" .xls files that are then e.mailed from the Mainframe to users, who can then open the files in Excel directly.

Office 2010 no longer allows "fake" .xls files to be opened, as it appears to require true Native .xls files.

XML files generated are too bloated for our Mainframe SMTP server to reliably handle.

What would be the next best option?  I tried raw HTML files, but then users have to go through extra steps to open the file in Excel.

Our Mainframe is being deprecated next year, so I'm hoping for something easyish, if anyone has any ideas?

Thanks!

kjk

[Cynthia_sas]

Hi:

  Actually, you CAN still use ODS HTML3 or ODS HTML or ODS MSOFFICE2K, ODS PHTML or ODS CHTML to generate HTML files. And, you can use .XLS as the "fake" file extension. Office 2007 and Office 2010 will, in fact, open those files. It will complain. It will complain with a somewhat snarky message, as described in this Tech Support note: http://support.sas.com/kb/31/956.html

But if you click YES (or change the Windows Registry, as described) then you can still open the file. Rather than change the Windows Registry, I just educate the users to click YES.

Otherwise, you can use ODS CSV to create a very compact, ASCII text file that can be opened in Excel.

cynthia

[NCNyrk]

Thanks...I am guessing I have a bad registry setting.  When I try to open the fake file in Office 2010, I get the snarky message, but there is no way to bypass the warning.  I will have to try it on my home machine, where I don't have to go through our internal Tech Support.

[garybald]

I'm using Office 2007 but I think the issue is the same.

I also use ods html3 on the mainframe to create files that I open with Excel.

I allocate the file like this:

FILENAME PDSEHTML 'TSM519.PDS.OUTPUT'

         DSNTYPE=LIBRARY

         DSORG=PO

         DISP=(NEW,CATLG,DELETE);

ODS HTML3 BODY = 'OUTPUT' (URL='OUTPUT.HTM') RS=NONE

          PATH = 'TSM519.PDS.OUTPUT' (URL=NONE)

          TRANTAB = ASCII;

ods listing close;

run;

other SAS code, usually proc tabulate

run;

ODS HTML3 CLOSE;

When I send the file in an email from the mainframe, I use this code

FILENAME MYMAIL EMAIL

      ('GARY.BALD@GLOBALCROSSING.COM')

      SUBJECT = 'SPREADSHEET'

      ATTACH=("TSM519.PDS.OUTPUT(OUTPUT)"

      CONTENT_TYPE='HTML');

The content_type is html rather than the default of text.

The file comes as an attachment with a .txt extension,

but I do a save as and give it .html extension.

When I open the file in Excel everything is formated.

I save it as an .xlsx file.

I don't know if this will work in Office 2010, but it's worth a try.  Good luck!

[NCNyrk]

Thanks for your idea; we may have to resort to save/rename, but I have a number of users to support who would probably not remember to do this every time, sigh.

[garybald]

I played around with this a little more and found out you can

add an extension option like below.  The file will come with .htm instead

of .txt.  Your users could open it with Excel and wouldn't have to

rename it to open it.  If they want an Excel file, they

would just have to remember to save it that way.

FILENAME MYMAIL EMAIL

      ('GARY.BALD@GLOBALCROSSING.COM')

      SUBJECT = 'SPREADSHEET'

      ATTACH=("TSM519.PDS.OUTPUT(OUTPUT)"

     CONTENT_TYPE='HTML'

     EXTENSION='HTM');

[FriedEgg]

You should use a valid mime content type.

ATTACH=("TSM519.PDS.OUTPUT(OUTPUT)"

  CT="application/vnd.ms-excel"

  EXT="xls"

NAME="my_excel_output" )