Ods tagsets.excelxp.... I can't open the resulting file

[SureshKasturi]

Ods tagsets.excelxp....
I can't open the resulting file.  It gives the following error.

Problem during load...Problem came up in the following areas during load,
Style
Table

The File Cannot be opened because of errors, Errors are listed in :C:\Documents and settings\242053\Local Settings\Temporary interent files\content.mso\486254CF.log.

ods tagsets.excelxp path="/server/server/"  FILE="final.xls" style=excelstyle ;

%do i= 1 %to &numrows;
ods tagsets.excelxp options(doc='help' );
ods tagsets.excelxp options (sheet_name="&&COUNTRY&i " Sheet_Interval ='none' pagebreaks='yes' zoom="85"
skip_space='0,0,0,0,0' frozen_rowheaders='yes'  embedded_titles='yes' row_heights='0,0,0,0,0,0,0' orientation="Landscape"
absolute_column_width='5,25,15,15,15,15,15,15,15,15,15,15,15,15,15,15,15,15,15,15' autofit_height='yes' PAGES_FITWIDTH="1" FitToPages="YES");

proc report data=sashelp.class;
run;
%end;

ods tagsets.excelxp;

Please help me.

[ballardw]

Since the macro %do - %end construct doesn't work in open code that might be part of the problem. If you want that to work you need to have in in a %macro macroname(); %mend; definition and then call with %macroname();

Also the note about style may mean there is something in the EXCELSTYLE that Excel doesn't like when reading XML. Try one of the other SAS supplied styles to see if that works.

[twocanbazza]

> The File Cannot be opened because of errors, Errors are listed in :C:\Documents and settings\242053\Local Settings\Temporary interent files\content.mso\486254CF.log.

Have you got the details from the log listed?

[Cynthia_sas · Accepted solution]

Hi, looking at the log in Local Settings might help, but in my experience, this error usually comes up because you have done something that violates the XML rules as set forth for the Spreadsheet Markup Language specification.

  I notice that, in addition to the error already noted of having the %DO loop out in open code (a problem), you also are using a custom style (STYLE=EXCELSTYLE) and since one of the complaints was about a style error by Excel, I would first suspect the STYLE template that you are using having some setting that is not supported by Excel and/or the XML spec. And you have a glaring error at the end of your program, where you show

ods tagsets.excelxp;

instead of ods tagsets.excelxp CLOSE; If you never close the destination, then SAS will be holding the file open for write access and Excel will complain about that.

  And the other thing to always check when you get these "unable to open" messages is whether you have the most current version of the tagset template for the ExcelXP destination. If you don't know what that means, or have never updated TAGSETS.EXCELXP, then depending on your version of SAS, you might want to open a track with Tech Support for help in this regard (and then, if your style template doesn't work with the updated TAGSET template, Tech Support can look at your style template, too.

  When I simplify your code, correct the macro issue, fix the close and use one of the SAS defined style templates, I do not have any trouble opening the output from this code (run in SAS 9.3 and opened with Excel 2010). Your use of sheet_interval='none' and doc='help' didn't make sense to me, so I didn't bother with them. I was going after the simplest %DO loop that would create multiple sheets, as a proof of how the macro loop worked.

  I will leave it to you and Tech Support to work out the other details.

cynthia

** 1) make a macro program with the %DO loop inside;

%macro multclass;
     
%do i= 1 %to &numrows;
ods tagsets.excelxp
   options(sheet_name="&&COUNTRY&i" embedded_titles='yes');
 
proc report data=sashelp.shoes(obs=5) nowd;
  title "Sheet for &&COUNTRY&i";
  where region = "&&country&i";
run;
%end;
    
%mend multclass;
  
  
** 2) create macro variables;
**    and invoke macro program;
%let country1 = Canada;
%let country2 = Africa;
%let country3 = Western Europe;
%let numrows = 3;
 
ods tagsets.excelxp path="c:\temp" (url=none)  FILE="final.xls" style=sasweb;
 
%multclass
 
ods _all_ close;

[cnwentsa]

Hi. sory for this poor indentation. In the code below, I am having an issue (I am running SAS 9.1.3) . column kl does not exist in my dataset. is I keep it, my output file can be open with excel, having missing values on column kl. If I remove it, my output file will not open and promts an error indicating table. What can be the reason why removing this column creates a file that I cannot open with ms excel? ods tagsets.excelxp   options(sheet_name="LISTE"             gridlines="yes" orientation="landscape" panelcols="1"                 embedded_titles="NO"                 frozen_headers="YES"                 frozen_headers="1" autofilter="yes"                 filter_colls="_all_"                 autofilter_table="1" row_heights="75"                 autofit_heights="no"                 );         proc report data=reftable.liste nowd ; column  actif Portefeuille  Conseiller  Poste majeure equipe ps Agregat Portefeuille kl ; define Portefeuille /style(header)=vertcl_header                                   style(column)=[just=center]; define equipe      /style(column)=[just=center] "Équipe"; define poste        /style(column)=[just=center] ;             define majeure      /style(column)=[just=center]; define ps          /style(column)=[just=center] format=fm_num.; define Agregat      /style(column)=[just=center] "Agrégat"; define actif      /computed style(column) ={tagattr='formula:=STRUCTURE_CAISSE!RC[0]'}; /* ;si(cherchev(RC[0];STRUCTURE_CAISSE!RC[0]:RC[0]);1;0) compute Actif; call define(_row_,"style","style={background=#ffffff}"); if actif eq 'X' then call define("actif","style","style={background=#99cc00}"); endcomp; */           run;  quit;

[ballardw]

On this forum it is preferable to start a new thread and reference an existing topic for a similar problem. Since the previous question had been marked "Answered" not many folks will check for new questions.

What ODS STYLE was active at the time you ran the code?

If you remove all of the Excelxp OPTIONS does the file open?

You didn't show an ODS tagsets.excelxp close statement, assuming there was one. If not then the file is still locked for writing by SAS.

And try without the tagattr as well.

[cnwentsa]

Hi there. as I mentionned in my question, there are two situations. 1- when I keep the last column kl in my column statement, the output file then , I can open my output file 2-when I remove it, I cannot open my file. You do not see the ods tagsets.excelxp close; just because this code is part of a larger code. So my question is: what hapends when I remove this empty column so that my file can no more be open with ms excel? Thanbks