The SAS Output Delivery System and reporting techniques

Generate Excel file for Office 2010 from Mainframe

Reply
Contributor
Posts: 21

Generate Excel file for Office 2010 from Mainframe

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

SAS Super FREQ
Posts: 8,868

Generate Excel file for Office 2010 from Mainframe

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

Contributor
Posts: 21

Generate Excel file for Office 2010 from Mainframe

Posted in reply to Cynthia_sas

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.

Contributor
Posts: 29

Generate Excel file for Office 2010 from Mainframe

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!

Contributor
Posts: 21

Generate Excel file for Office 2010 from Mainframe

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.

Contributor
Posts: 29

Generate Excel file for Office 2010 from Mainframe

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');

Trusted Advisor
Posts: 1,301

Generate Excel file for Office 2010 from Mainframe

You should use a valid mime content type.

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

  CT="application/vnd.ms-excel"

  EXT="xls"

NAME="my_excel_output" )

Ask a Question
Discussion stats
  • 6 replies
  • 1951 views
  • 0 likes
  • 4 in conversation