Sample 43470: How to add multiple worksheets per workbook with the MSOffice2K destination
The HTML format that the MSOffice2K tagset generates does not support multiple worksheets per workbook like the ExcelXP destination, which emulates the XML spreadsheet format. With the ExcelXP destination, each new output object is added to a new sheet by default. This is the desired method of generating multiple worksheets per workbook. If the HTML file format is needed and multiple worksheets per workbook is desired, the sample MSOffice2K_X tagset can be used to generate the multiple worksheets.
The sample MSOffice2K_X tagset allows you to pass the path of the an existing HTML file which can be added to an existing workbook as a worksheet. This path is added using the WORKSHEET_SOURCE= tagset option. To specify multiple worksheets per workbook with this option, specify each file separated by commas. To name the worksheet, prefix the file with the desired sheet name. If no sheet name is given, the default sheet name is "Table 1". This method requires that the HTML file be generated first.
See the example code on the Full Code tab for an illustration.
These sample files and code examples are provided by SAS Institute
Inc. "as is" without warranty of any kind, either express or implied, including
but not limited to the implied warranties of merchantability and fitness for a
particular purpose. Recipients acknowledge and agree that SAS Institute shall
not be liable for any damages whatsoever arising out of their use of this material.
In addition, SAS Institute will provide no support for the materials contained herein.
This sample illustrates how to create multiple worksheets per workbook using the TAGSETS.MSOffice2K_X sample tagset. The code generates two separate HTML files; one contains a graph and the other contains a table.
goptions xpixels=700 ypixels=500;
ods msoffice2k path="C:\temp"(url=none) file="file1.html" newfile=output;
proc gchart data=sashelp.prdsale;
pie product;
run;
quit;
proc print data=sashelp.prdsale;
run;
ods msoffice2k close;
/********************************************************************************/
/* The MSOffice2K_X tagset can be downloaded from the URL below or by using the */
/* URL access method shown below, which adds the tagset to the work location. */
/* http://support.sas.com/rnd/base/ods/odsmarkup/msoffice2k/index.html */
/********************************************************************************/
ods path(prepend) work.templat(update);
filename temp url "http://support.sas.com/rnd/base/ods/odsmarkup/msoffice2k/msoffice2k_x.sas"; %include temp;
/************************************************************/
/* Add files which will be included as worksheets using the */
/* Worksheet_Source= option of the MSOffice2K_X tagset. */
/************************************************************/
ods tagsets.msoffice2k_x path="c:\temp" file="test.xls" style=statistical
options(worksheet_source="c:\temp\file1.html,c:\temp\file2.html");
data _null_;
file print;
put _all_;
run;
ods tagsets.msoffice2k_x close;
/*****************************************************************/
/* Add files which will be included as worksheets using the */
/* Worksheet_Source= option and the sheet name which is appended */
/* prior to the "#". */
/*****************************************************************/
ods tagsets.msoffice2k_x path="c:\temp" file="test.xls" style=statistical
options( worksheet_source="Graph_ouput#c:\temp\file1.html,
Table_outpu#c:\temp\file2.html" );
data _null_;
file print;
put _all_;
run;
ods tagsets.msoffice2k_x close;
These sample files and code examples are provided by SAS Institute
Inc. "as is" without warranty of any kind, either express or implied, including
but not limited to the implied warranties of merchantability and fitness for a
particular purpose. Recipients acknowledge and agree that SAS Institute shall
not be liable for any damages whatsoever arising out of their use of this material.
In addition, SAS Institute will provide no support for the materials contained herein.
This sample illustrates how to add multiple worksheets per workbook with the MSOffice2K destination.
| Date Modified: | 2011-09-13 13:41:28 |
| Date Created: | 2011-06-08 17:52:39 |
Operating System and Release Information
| SAS System | Base SAS | z/OS | | |
| Z64 | | |
| OpenVMS VAX | | |
| Microsoft® Windows® for 64-Bit Itanium-based Systems | | |
| Microsoft Windows Server 2003 Datacenter 64-bit Edition | | |
| Microsoft Windows Server 2003 Enterprise 64-bit Edition | | |
| Microsoft Windows XP 64-bit Edition | | |
| Microsoft® Windows® for x64 | | |
| OS/2 | | |
| Microsoft Windows 95/98 | | |
| Microsoft Windows 2000 Advanced Server | | |
| Microsoft Windows 2000 Datacenter Server | | |
| Microsoft Windows 2000 Server | | |
| Microsoft Windows 2000 Professional | | |
| Microsoft Windows NT Workstation | | |
| Microsoft Windows Server 2003 Datacenter Edition | | |
| Microsoft Windows Server 2003 Enterprise Edition | | |
| Microsoft Windows Server 2003 Standard Edition | | |
| Microsoft Windows Server 2003 for x64 | | |
| Microsoft Windows Server 2008 | | |
| Microsoft Windows Server 2008 for x64 | | |
| Microsoft Windows XP Professional | | |
| Windows 7 Enterprise 32 bit | | |
| Windows 7 Enterprise x64 | | |
| Windows 7 Home Premium 32 bit | | |
| Windows 7 Home Premium x64 | | |
| Windows 7 Professional 32 bit | | |
| Windows 7 Professional x64 | | |
| Windows 7 Ultimate 32 bit | | |
| Windows 7 Ultimate x64 | | |
| Windows Millennium Edition (Me) | | |
| Windows Vista | | |
| Windows Vista for x64 | | |
| 64-bit Enabled AIX | | |
| 64-bit Enabled HP-UX | | |
| 64-bit Enabled Solaris | | |
| ABI+ for Intel Architecture | | |
| AIX | | |
| HP-UX | | |
| HP-UX IPF | | |
| IRIX | | |
| Linux | | |
| Linux for x64 | | |
| Linux on Itanium | | |
| OpenVMS Alpha | | |
| OpenVMS on HP Integrity | | |
| Solaris | | |
| Solaris for x64 | | |
| Tru64 UNIX | | |
