XLS, XLSX, and XLSM subcommands (OUTPUT EXPORT Command)

The XLS, XLSX, and XLSM subcommands export output in Excel 97-2004, Excel 2007 and higher, and Excel 2007 and higher, macro-enabled formats respectively. Pivot table rows, columns, and cells are exported as Excel rows, columns, and cells, with all formatting attributes intact -- for example, cell borders, font styles, and background colors. Text output is exported with all font attributes intact. Each line in the text output is a row in the Excel file, with the entire contents of the line contained in a single cell. Charts, tree diagrams, and model views are included in PNG format.There are no macros in the output; the XLSM option allows you to append output to existing macro-enabled (.xlsm) Excel files.

DOCUMENTFILE keyword

The required DOCUMENTFILE keyword specifies the name of the Excel document. The keyword is followed by an equals sign and a quoted file specification, as in:

OUTPUT EXPORT
  /XLS DOCUMENTFILE='/mydocs/myresults.xls'.

OPERATION keyword

The OPERATION keyword specifies the type of operation to be performed by the export. The keyword is followed by an equals sign and one of the following alternatives:

CREATEFILE. Create an Excel file. If a file with the specified name already exists, it is overwritten. This is the default.

CREATESHEET. Create a new worksheet within an Excel file. If a worksheet with the specified name (specified on the SHEET keyword) already exists in the file , that worksheet is overwritten. If the file doesn't already exist, a new file is created with a worksheet with the specified name.

MODIFYSHEET. Modify the contents of the specified worksheet. Content is added to the worksheet based on the specification on the LOCATION keyword. You must specify a sheet name with the SHEET keyword. If you modify an existing Excel 97-2004 worksheet, charts, model views, and tree diagrams are not included in the exported output.

SHEET keyword

The SHEET keyword specifies the name of the worksheet to which items will be exported. The keyword is followed by an equals sign and a sheet name enclosed in quotes.

  • Sheet names cannot exceed 31 characters and cannot contain forward or back slashes, square brackets, question marks, or asterisks.
  • If the specified worksheet doesn't exist in the Excel file, a new worksheet with that name will be created, regardless of operation method.
  • The SHEET keyword is required with OPERATION=MODIFYSHEET. It is optional with the other operation methods.

LOCATION keyword

The LOCATION keyword specifies the location within the worksheet for the exported items. The keyword is followed by an equals sign and one of the following alternatives:

LASTCOLUMN. Add exported items after the last column. Exported items will be added after the last column, starting in the first row, without modifying any existing contents. This is a good choice for adding new columns to an existing worksheet. This is the default.

LASTROW. Add exported items after the last row. Exported items will be added after the last row, starting in the first column, without modifying any existing contents. This is a good choice for adding new rows to an existing worksheet.

STARTCELL('cell'). Add exported items beginning at a specific cell location. Exported items will be added to the worksheet starting at the specified cell location. The cell reference is required and must be enclosed in quotes. Any existing content in the area where the exported items are added will be overwritten.
Warning: STARTCELL is not designed to overwrite existing worksheet data. The export can result in corrupted tables and an unusable worksheet in cases where STARTCELL overwrites existing worksheet data.

NOTECAPTIONS keyword

The NOTESCAPTIONS keyword controls the inclusion of pivot table footnotes and captions in the exported document. The keyword is followed by an equals sign and one of the following alternatives:

YES. Include footnotes and captions. This is the default.

NO. Do not include footnotes and captions.  

Example

OUTPUT EXPORT
  /XLS DOCUMENTFILE='/mydocs/myresults.xls'
   OPERATION=MODIFYSHEET
   SHEET='Region 1'
   LOCATION=STARTCELL('B2').