Hi:
If you use TAGSETS.EXCELXP's suboption for documentation:
[pre]
ods tagsets.excelxp file=..... options(doc='Help');
or
ods tagsets.excelxp file=..... options(doc='All');
[/pre]
you will see (in the SAS log) the options that are available for adjusting column width:
[pre]
Width_Fudge: Default Value '0.75'
Values: None, Number.
By default this value is used along with Width_Points and column width
to calculate an approximate width for the table columns.
width = Data_Font_Points * number_Of_Chars * Width_Fudge.
If 'none' this feature is turned off.
Width_Points: Default Value 'None'
Values: None, Number.
By Default the point size from the data or header style
elements are used to calculate a pseudo column width.
The column width is calculated from the given column width or
the length of the column's header text. If the header is bigger.
In the case the header length is used, so is the header's point size.
This value overrides that point size.
This value is used along with WidthFudge and column width
to calculate an approximate width for the table columns.
width = Width_Points * number_Of_Chars * Width_Fudge.
Default_Column_Width: Default Value 'None'
Values: None, Number, list of numbers.
Most procedures provide column widths, but occasionally a column
will not have a width. Excel will resize the column to fit any
numbers but will not auto-size for character string headings.
In the case that a column does not have a width, this value will be
used instead. The value should be the width in characters.
If the value of this option is a comma separated list.
Each number will be used for the column in the same position. If
the table has more columns, the list will start over again.
Absolute_Column_Width: Default Value 'None'
Values: None, Number, list of numbers.
This option works similarly to the default column width option
The difference is that these widths will be used regardless
of any column widths the procedure might provide.
The value should be the width in characters.
If the value of this option is a comma separated list.
Each number will be used for the column in the same position. If
the table has more columns, the list will start over again.
[/pre]
There is no suboption for doing an autofit on width. The Spreadsheet Markup Language XML is not the same as VBA -- so if there is a way to autofit width, it would have to be an XML method, not a VBA method.
When I use Notepad to look in the XML file created by SAS (for a simple PROC PRINT of SASHELP.CLASS using TAGSETS.EXCELXP), I see this ss:AutoFitWidth attribute:
[pre]
<Table ss:StyleID="_body">
<ss:Column ss:AutoFitWidth="1" ss:Width="60"/>
<ss:Column ss:AutoFitWidth="1" ss:Width="60"/>
<ss:Column ss:AutoFitWidth="1" ss:Width="60"/>
<ss:Column ss:AutoFitWidth="1" ss:Width="60"/>
<ss:Column ss:AutoFitWidth="1" ss:Width="60"/>
<ss:Column ss:AutoFitWidth="1" ss:Width="60"/>
[/pre]
So that means that ODS TAGSETS.EXCELXP is sending the AutoFitWidth attribute with a value of 1 and an explicit ss:Width value.
From the Microsoft site:
http://msdn.microsoft.com/en-us/library/aa140066(v=office.10).aspx
it says:
[quote]
Attribute: ss:AutoFitWidth
Description: If this attribute is specified as True ("1"), it means that this column should be autosized for numeric and date values only. We do not autofit textual values.
If both ss:Width and ss:AutoFitWidth exist, the behavior is as follows:
ss:AutoFitWidth="1" and ss:Width is unspecified: Autofit the column width to fit the content.
ss:AutoFitWidth="1" and ss:Width is specified: Set the column to the specified width and only autofit if the size of the content is larger than the specified width.
ss:AutoFitWidth="0" and ss:Width is unspecified: Use the default column width.
ss:AutoFitWidth="0" and ss:Width is specified: Use the specified width.
[/endquote]
I think that this sentence bears repeating (from the Microsoft quote above):
We do not autofit textual values.
Since that statement is coming from Microsoft, that means TAGSETS.EXCELXP must live by these rules. But, since there are suboptions to explicitly specify a width, you can use those suboptions -- either Default_Column_Width or Absolute_Column_Width should work for you. You will find an example of setting column widths in this user group paper:
http://www.nesug.org/proceedings/nesug08/ap/ap06.pdf
cynthia