topic Re: error on worksheet setting in ODS and Base Reporting

error on worksheet setting

steve_citi — Tue, 29 Dec 2009 18:33:03 GMT

I do not have the directory location where the error messages for this type of error are placed. Is there any other way to diagnose what this error is? I am using the latest excelxp tagset (1.86).

Can I somehow redirect the error messages to another location?

Re: error on worksheet setting

Cynthia_sas — Tue, 29 Dec 2009 21:26:46 GMT

Hi:
If you have a worksheet error when opening a TAGSET.EXCELXP spreadsheet, the error comes from Excel having a problem with the XML or the STYLE. Usually, the error file from Excel is put in Documents and Settings under Local Application Data or something...I don't remember the exact location.

Whether you -can- redirect the error messages to another location would be a Microsoft Office setting.

cynthia

Re: error on worksheet setting

Linda_V — Wed, 30 Dec 2009 21:05:47 GMT

I am using SAS on a server platform and trying to output a multiple sheet xls. I am trying to output the xls using Style=Normal and getting a Worksheet setting error when I try to open up the xls. I can use Style=EGDefault it will work, however I do not want those settings for the report.

I can not run the Tagsets because I don't have access to where the tagsets need to write to.. so I am trying to get some sort of work-around to work...

Any solutions??

Thanks..

Re: error on worksheet setting

Cynthia_sas — Wed, 30 Dec 2009 22:03:27 GMT

Hi:
When you use TAGSETS.EXCELXP to create a multi-worksheet file, you are creating a Microsoft Excel XML file that conforms to the Office 2003 specification for Spreadsheet Markup Language XML. So, you are not creating a "true binary .XLS file" -- you are creating an XML file that Excel 2003 knows how to open.

The issue with STYLE=NORMAL is interesting. On my machine, I can run a job using STYLE=NORMAL with TAGSETS.EXCELXP version SAS 9.1.3, v1.75, 07/26/07 and have no issues at all on opening the file. Can you examine the SAS log from your server job? I wonder whether you are getting a message like this in the log:
[pre]
WARNING: Style NORMAL not found; Default style will be used instead.
[/pre]

I don't understand what you mean by not being able to "run the Tagsets" -- the NORMAL style is a STYLE template, not a TAGSET template. You would not necessarily have to do anything with a TAGSET template in order to switch to a different style template. If STYLE=NORMAL isn't on the server machine, it may be that you will have to use EGDEFAULT or one of the other styles that is available on the server. One thing that you can do is submit a PROC TEMPLATE job -- to run on the server machine -- to see what the list of available style templates is.

Can you post the code you are running -- perhaps using SASHELP.CLASS or a simple PROC PRINT if you don't want to show your whole program? Are you submitting the code via the EG interface or as a stored process or through the SAS Add-in for Microsoft Office???

cynthia

Re: error on worksheet setting

deleted_user — Wed, 06 Jan 2010 14:01:02 GMT

C:\Documents and Settings\\Local Settings\Temporary Internet Files\Content.MSO

Check under the above folder. would be your user. what ever it is.

As Cynthia stated when you open a SAS generated XLS/XML file with errors, a SAS log file will be created in the above location. That log file gives you information on where you went wrong like STYLE , XML or unspecified. I usually see unspecified
error if my Excel file has too much of data.

Addy

Re: error on worksheet setting

Peter_C — Fri, 08 Jan 2010 15:29:47 GMT

the path that SASmatt reports is like one of those "magic" keywords.
Microsoft offers no way to redirect it, so it may be very reliable - at least until vista or windows 7 adapts the profile path or %temp% path
PeterC