The stpBegin macro does a lot for us, but for some of the customizations we will need to help it out a little by adding some logic in a DATA step before calling stpBegin.
For HTML opened in Excel, we just need to make sure that the Content-type header for Excel is used, e.g.,
rc = stpsrv_header('content-type','application/vnd.ms-excel');
We will do this by specifying a value of EXCEL for _odsDest (which we reset to HTML after generating the Content-type header).
We need the same Content-type header for ExcelXP. But due to an issue with how Office 2007 works, we also need to provide a Content-disposition header that specifies it is an XML file, e.g.,
rc = stpsrv_header('Content-disposition','attachment; filename="_.xml"');
Note the use of _.xml as the filename. Our generic Stored Process can't infer a reasonable name, so we just use the _. Of course, you could make this a parameter - but that is left as exercise for the reader ;-).
For the TableEditor tagset, we just need to pass a few options into the stpBegin macro using the global macro variable _odsOptions. There are lots of parameters the we could use. For the purposes of this example, a few that you will almost certainly want to support are:
|| " pageheight='&pageheight'"
|| " pagewidth='&pagewidth'"
|| " sort='&sort'"
|| " frozen_rowheaders='&frozen_rowheaders'"
|| " frozen_headers='&frozen_headers')"));
The complete runMacro Stored Process can be found on sasCommunity.