topic Generate Excel file for Office 2010 from Mainframe in ODS and Base Reporting

Generate Excel file for Office 2010 from Mainframe

NCNyrk — Wed, 02 Nov 2011 19:02:27 GMT

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

Generate Excel file for Office 2010 from Mainframe

Cynthia_sas — Wed, 02 Nov 2011 19:23:25 GMT

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

Generate Excel file for Office 2010 from Mainframe

garybald — Thu, 03 Nov 2011 18:28:54 GMT

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!

Generate Excel file for Office 2010 from Mainframe

NCNyrk — Thu, 03 Nov 2011 20:02:58 GMT

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.

Generate Excel file for Office 2010 from Mainframe

NCNyrk — Thu, 03 Nov 2011 20:03:47 GMT

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.

Generate Excel file for Office 2010 from Mainframe

garybald — Tue, 08 Nov 2011 21:23:33 GMT

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

Generate Excel file for Office 2010 from Mainframe

FriedEgg — Tue, 08 Nov 2011 23:15:15 GMT

You should use a valid mime content type.

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

CT="application/vnd.ms-excel"

EXT="xls"

NAME="my_excel_output" )