Create table mysheets as
Select distinct tranwrd(scan(memname,1,'$'),"'","") as Sheetid
from sashelp.vstabvw where libname="XLS" and upcase(tranwrd(scan(memname,1,'$'),"'","")) ne "TOC";
proc datasets library=Xls nodetails; delete TOC 'TOC$'n; quit;
Data xls.TOC ;
Not sure what's up with your code. However, as an alternative, this code, using TAGSETS.EXCELXP does create a TOC as the first page of a multi-sheet workbook. I used the version of the ExcelXP tagset template dated (SAS 9.1.3, v1.75, 07/26/07).
How Excel wants the TOC link specified looks like this in the Spreadsheet Markup Language XML written by ExcelXP. Note that in the XML Markup, the contents link is set by the ss:HRef= attribute.
But, I don't know how the LIBNAME engine for Excel generates an internal hyperlink. This might actually be a question for Tech Support.
Example of XML SpreadsheetML contents written by TAGSETS.ExcelXP:
Thanks! however I think I was not enough clear with this situation...
I've got an excel file with a bunch of sheets, and every month i'll be adding/deleting some sheets. What my code does is:
1) read all those sheets through a EXCEL LIBNAME and put them into a SAS table (that the SQL bit).
2) Clear the content of the TOC sheet (if exist)
3) Write back to excel all worksheet's name formatted as links/hiperlinks. Each link will point out one sheet.
4) Clear the libname to release the file.
Everything works fine a part from the following issue:
Rather than getting this in every cell:
=HYPERLINK("#"&CELL("address",'Dictionary Tables'!A1),"Dictionary Tables")
I am getting this:
'=HYPERLINK("#"&CELL("address",'Dictionary Tables'!A1),"Dictionary Tables")
which is being read as text when actually is a formula.
At first glance of the SAS code you sent in your initial post, I can see that your LINK variable assignment starts with a double-quote, however it does not end with a same double-quote (I see a single-quote in your code submitted).