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
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
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.
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!
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.
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');
You should use a valid mime content type.
ATTACH=("TSM519.PDS.OUTPUT(OUTPUT)"
CT="application/vnd.ms-excel"
EXT="xls"
NAME="my_excel_output" )
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.