ods tagsets.excelxp INDEX option

[Yaya1024]

Hi Gurus,

I have generated an excel report which contains several tabs with SAS 9.4. I want to produce a table of contents as the first sheet which contains all the sheet names with links that could jump to corresponding tabs.

I used the INDEX='yes' option. But the links in the result TOC is based on the full path of the excel document. If I move the excel or send it buy email, the links will become invalid. Is there any way to fix this issue?

My code is as below:

ods escapechar="^";

ods tagsets.excelxp options(sheet_name="&xlsheetname" index='yes'  frozen_headers='yes' frozen_RowHeaders='2' Zoom='80' embedded_titles='yes') ;

title justify=left link="#'Contents'!B1"  color=blue underlin=1 '^S={font_style=italic font_size=11pt} TAKE ME TO THE CONTENTS TABLE';

proc report data=&printdata1 nowd  split='*' spacing=1 headline missing;

..........; run;

ods tagsets.excelxp options(sheet_name="&xlsheetname" index='yes'  frozen_headers='yes' frozen_RowHeaders='2' Zoom='80' embedded_titles='yes') ;

title justify=left link="#'Contents'!B1"  color=blue underlin=1 '^S={font_style=italic font_size=11pt} TAKE ME TO THE CONTENTS TABLE';

proc report data=&printdata2 nowd  split='*' spacing=1 headline missing;

..........; run;

ods tagsets.ExcelXP close;

Thanks a lot!

[Chevell_sas]

See the SAS Note below which will allow you to create relative links in the index sheet which will allow them to function correctly.

http://support.sas.com/kb/45107