Tagsets.ExcelXP, conditional CALL DEFINEs for URL problems

[RobW]

Hi,

When I use a compute block in proc report to determine if a cell's content should url/link to another worksheet, the previously declared style formats for that cell are lost. When I include a separate call define for 'style' in the same conditional block to get it back in sync with the original formatting, it has no impact. An example is in my DEFINE statements I am telling it to have a font_face=Verdana for column 'x'. When the cell is hyperlinked due to the condition being met, it changes the font_face to arial and also changes the font_size. My separate call define for 'style' in the same compute block has no effect on changing it back to Verdana. Can this be fixed?? What am I missing?

r

[Cynthia_sas]

Hi:
That formatting may change because of the way that Microsoft displays hyperlinks. I would assume that the hyperlink ability is the most important thing in the cell. So I would guess that the number gets plopped in the cell and then the hyperlink gets applied. I don't think that PROC REPORT is clobbering the style you set. I think that Excel is clobbering the style.

The reason I think it's Excel and not Proc Report is that if you run the program below, you will see that age and height columns are both Verdana 14pt when the file opens in Excel. When the height is over 69, the Verdana and 14pt are still in effect and only the foreground color changes to red.

[pre]
ods tagsets.excelxp file='c:\temp\class_style.xml' style=egdefault;
proc report data=sashelp.class nowd;
column name sex age height;
define name / order;
define sex / order;
define age / display style(column)={font_size=14pt font_face=Verdana} ;
define height / display style(column)={font_face=Verdana font_size=14pt};
compute height;
if height ge 69 then
call define (_col_,'style','style={foreground=red font_weight=bold}');
endcomp;
run;
ods tagsets.excelxp close;
[/pre]

You might investigate on the Microsoft site to see how they format hyperlinks by default. Or you might wish to work with Tech Support on this issue, but, I suspect that if it's an Excel issue there may not be a workaround from the SAS side of things.

cynthia

[RobW]

thanks Cynthia. I see when i open the file in a text editor that it creates kind of a default style for hyperlinked cells with no variation like I see for my non-hyperlinked cells. Is there another tagset worth looking at?

[Cynthia_sas]

Hi:
I don't think another tagset would help you. There's only one TAGSETS.EXCELXP -- the only other choices would be HTML-based templates.

I suppose there is a -chance- that you could modify the ExcelXP tagset to write the kind of style you want for the hyperlink. But, I looked at the tagset and didn't see an obvious place to make the change.

You might want to check with Tech Support to see whether they can help you figure out if the tagset template for ExcelXP can be modified.

cynthia