Date:         Mon, 18 Apr 2011 11:28:39 -0400
Reply-To:     Ben Herman <baherman@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Ben Herman <baherman@GMAIL.COM>
Subject:      Re: tagsets.excelxp multiple files when HTML is on

No Joy here! You ever have one of those days where nothing makes sense?

I appologize that  my sample code was muddled (I accidentally dumped the
options when I shortened it up for posting).  I had originally included
the sheetname.  So here's the kicker.  I decided to do a little more
trouble shooting. Opened up SAS ran my code -- got the error.  Opened up a
second session, copied the code in sections -- No error.  Opened up a
third session, copied all the code from the first session into the third,
ran with no error.  Apparently I have poltergeists.

For those curious about the actual errors I got:
Excel throws an error (for the first file created): "Problems During Load":
Worksheet Setting
Style

Here is a sample of the errors Excel writes to %USERNAME%
AppData\Local\Microsoft\Windows\Temporary Internet Files\Content.MSO

XML ERROR in Style
REASON: Bad Value
FILE: [%USERNAME%]\junk1.xml
GROUP: Styles
TAG: Style
ATTRIB: Parent
VALUE: _body
[there are about 209 lines of this and then]

XML ERROR in Worksheet Setting
REASON: Bad Value
FILE: C:\Users\bherman\junk1.xml
GROUP: Worksheet
TAG: Table
ATTRIB: StyleID
VALUE: _body


For the second file created it throws the same error dialog, but now the
error is: "Missing file: res://\\ieframe.dll/ErrorPageTemplate.css"

And it didn't matter whether I used xls or xml.  But now I wonder if there
is something funky with my sasuser.registry.

Thanks for the help.
Ben

On Sat, 16 Apr 2011 14:59:43 +0000, DUELL, BOB (ATTCINW) <bd9439@ATT.COM>
wrote:

>
>> Date: Fri, 15 Apr 2011 22:23:24 +0000
>> From: bd9439@ATT.COM
>> Subject: Re: tagsets.excelxp multiple files when HTML is on
>> To: SAS-L@LISTSERV.UGA.EDU
>>
>> Hi Ben,
>>
>> Try turning off the default ods listing destination and naming the
first sheet. With respect to the error message you get from Excel, that's
because the tagset is actually creating an XML file, not an Excel binary.
Change the file type from ".xls" to ".xml" and Excel will no longer
complain.
>>
>> The "xml" file type should be registered in Windows be opened by Excel
by default (at least it does for me). If it doesn't work automatically (by
double-clicking) it means some other software has "taken ownership" of the
xml file type. In that case, just open the file from inside Excel (normal
file-> open), or change the "default" action back to Excel.
>>
>> In other words:
>>
>> ods listing close
>> ods tagsets.excelxp file="junk.xml" options(sheet_name="FirstSheet")
>> style=minimal
>> ;
>> proc print data=sashelp.class noobs label;
>> id Name;
>> var Sex;
>> run;
>>
>> ods tagsets.excelxp options(sheet_name="Summary");
>>
>> proc freq data=sashelp.class;
>> tables sex/missing nocum ;
>> run;
>>
>> ods tagsets.excelxp close;
>> ods listing;
>>
>> Good luck,
>>
>> Bob
>>
>> -----Original Message-----
>> From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
Ben Herman
>> Sent: Friday, April 15, 2011 1:26 PM
>> To: SAS-L@LISTSERV.UGA.EDU
>> Subject: tagsets.excelxp multiple files when HTML is on
>>
>> So I'm having a bit of a problem with tagsets.excelxp and was wondering
if
>> anyone knows what is happening.
>>
>> The code below attempts to generate a simple excel file with 2 sheets
>> using the "minimal" style. when I run this on SAS 9.2M3 with excelXP
>> (v1.11.6) I run into a bit of a snag. in interactive if I have HTML
>> results being created by default (Preferences -> Results->"Create HTML"
>> use work folder), I get 2 files Junk.xls and junk2.xls (rather than
>> Junk.xls with 2 sheets) this goes away if I uncheck "Create Html" in my
>> settings. However if I use a style (as below) I get an error when Excel7
>> attempts to open the worksheets (somthing about correupt tables and
>> such). Other than the obvious workaround of stop doing that, does anyone
>> know how I can fix this? is it a problem with the template definition?
>> Thanks,
>> Ben
>>
>> ods tagsets.excelxp file="junk.xls"
>> style=minimal
>> ;
>> proc print data=sashelp.class noobs label;
>> id Name;
>> var Sex;
>>
>> run;
>>
>> ods tagsets.excelxp options(sheet_name="Summary");
>>
>> proc freq data=sashelp.class;
>> tables sex/missing nocum ;
>> run;
>>
>> ODS tagsets.excelxp close;