Using ODS Tagsets.Excelxp Options with AMO\Add-in for Microsoft Office?

[wcp_fnfg]

Is this possible?  I want my stored procedure to contain things like the column width, header, footer.  When I comment out the file= line in the tagset, I get no body file errors.

[RW9]

Not sure you understand the technology here.  AMO Add-in is a SAS tool which plugs into the Office Architecture opening up the possibility to directly open and filter datasets within Excel for instance.  Ods tagsets.excelxp is a tagset which is used in conjunction with some data to create a text based XML (markedup) format document, which when opened in Excel is read and interpreted by the XML parser and then displayed within Excel.

So the two tools come from very different approaches and I don't see why you would use both.  So to re-iterate, addin allows direct access to the datasets - its quite basic, tagset output creates a file which Excel can read an interpret.   If you want to look at datasets then use add in, if you want funky output with formats etc. then create a file using a tagset.

[wcp_fnfg]

Oh, I understand the technology.  I don't believe you've answered my question about whether I can control excel parameters like headers\footers\column widths with AMO or not.

[Cynthia_sas]

Hi:

  The SAS Add-in for Microsoft Office does not "receive" TAGSETS.EXCELXP results from a stored process. The only 3 result types that AMO can "receive" from a stored process at the present time are: CSV, HTML and SASReport XML. So the EXCELXP options for setting column widths and headers/footers will not work for your stored process. CSV, of course, does NOT support style "cosmetics" like column widths and headers and footers. HTML -- might or might not -- Perhaps if you override _ODSDEST to be ODS MSOFFICE2K, you can use the HTMLSTYLE= override with the code (if it is PROC PRINT, PROC REPORT or PROC TABULATE) that will work for column width. This paper http://support.sas.com/resources/papers/proceedings11/266-2011.pdf  has several examples of using HTMLSTYLE with ODS MSOFFICE2K HTML, including changing the width attribute. If you want to create TAGSETS.EXCELXP output, the only Platform client apps that can "receive" TAGSETS.EXCELXP output are: the Information Deliver Portal and invoking your stored process via URL and the Stored Process Web Application (SPWA).

  I don't have a platform install to practice on, but you should be able to run this code as a stored process (after changing it appropriately), if the NAME column is wider than usual, then you at least have a way to make the columns wide. I am not sure about header/footer and using HTML for a stored process. ODS MSOFFICE2K does not have any header/footer options. Or, you might try ODS MSOFFICE2K_X but the issue is that with the SAS Add-in, the MSOFFICE2K_X destination isn't straight HTML -- it's HTML with a mix of Microsoft XML -- so I don't know whether AMO will accept this form of HTML. You might want to open a track with Tech Support on this for help with the Platform and what can be done with a Stored Process and AMO.

Cynthia

ods msoffice2k file='c:\temp\testit.html'

               style=sasweb;

proc print data=sashelp.class;

title 'My Title';

var name / style(data)={htmlstyle="width:'160pt'"};

var age sex height weight;

run;

ods msoffice2k close;

change code for stored process:

*ProcessBody;

%let _odsdest=msoffice2k;

%let _odsstyle = sasweb;

%stpbegin;

proc print data=sashelp.class;

title 'My Title';

var name / style(data)={htmlstyle="width:'160pt'"};

var age sex height weight;

run;

%stpend;;