Date: Wed, 12 May 2010 08:36:18 -0700
Reply-To: "Choate, Paul@DDS" <Paul.Choate@DDS.CA.GOV>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: "Choate, Paul@DDS" <Paul.Choate@DDS.CA.GOV>
Subject: Re: Activate First Worksheet in Excel Workbook Using DDE Command
(was Autosize Excel Columns from SAS)
Content-Type: text/plain; charset="us-ascii"
Hey Jack - I'm glad that helped.
To leave the first sheet active I'd try one of the following two methods:
WORKBOOK.ACTIVATE("sheet_name") if you know the sheet name - otherwise you can't choose it by order number.
WORKBOOK.PREV() moves back one sheet, if you're on the first sheet it has no effect - so loop across it enough times to make sure it moves to the first sheet.
DDS Data Extraction
From: Jack Clark [mailto:firstname.lastname@example.org]
Sent: Wednesday, May 12, 2010 7:19 AM
To: Choate, Paul@DDS; SAS-L@LISTSERV.UGA.EDU
Subject: Activate First Worksheet in Excel Workbook Using DDE Command (was Autosize Excel Columns from SAS)
Thanks to Paul for the column.width command with DDE. I have
implemented this and it is working fine.
The code below loops through the 24 worksheets in my Excel file and does
the autofit for column width on each sheet. The file is then saved and
Excel is closed.
* autofit cell size in all worksheets ;
* be sure loop is set to total number of worksheets in workbook ;
do i = 1 to 24;
put '[column.width(0,"c1:c5",false,3)]'; *-- AUTO FIT COLUMN WIDTH;
The final thing I would like to do is make the first sheet in the
workbook active before saving. The commented out line above does not
work and I have not had any luck yet searching online.
Does anyone know the proper syntax to activate the first worksheet in
the workbook using DDE commands? Thank you.
Senior Research Analyst
email@example.com -----Original Message-----
From: Choate, Paul@DDS [mailto:Paul.Choate@dds.ca.gov]
Sent: Tuesday, May 11, 2010 7:11 PM
To: Jack Clark; SAS-L@LISTSERV.UGA.EDU
Subject: RE: Autosize Excel Columns from SAS
Hey Jack -
I use DDE - look at the COLUMN.WIDTH command.
This adjusts the first seven columns:
Macro Sheets Only
Equivalent to choosing the Width command on the Column submenu of the
Format menu. Changes the width of the columns in the specified
COLUMN.WIDTH(width_num, reference, standard, type_num, standard_num)
COLUMN.WIDTH?(width_num, reference, standard, type_num, standard_num)
Width_num - specifies how wide you want the columns to be in units of
one character of the font corresponding to the Normal cell style.
Width_num is ignored if standard is TRUE or if type_num is provided.
Reference - specifies the columns for which you want to change the
If reference is specified, it must be either an external reference to
the active worksheet, such as !$A:$C or !Database, or an R1C1-style
reference in the form of text, such as "C1:C3", "C[-4]:C[-2]", or
If reference is a relative R1C1-style reference in the form of text, it
is assumed to be relative to the active cell.
If reference is omitted, it is assumed to be the current selection.
Standard - is a logical value corresponding to the Standard Width
command from the Column submenu on the Format menu.
If standard is TRUE, Microsoft Excel sets the column width to the
currently defined standard (default) width and ignores width_num.
If standard is FALSE or omitted, Microsoft Excel sets the width
according to width_num or type_num.
Type_num - is a number from 1 to 3 corresponding to the Hide, Unhide, or
AutoFit Selection commands, respectively, on the Column submenu of the
Type_num Action taken
1 Hides the column selection by setting the column width to 0
2 Unhides the column selection by setting the column width to the
value set before the selection was hidden
3 Sets the column selection to a best-fit width, which varies from
column to column depending on the length of the longest data string in
Standard_num - specifies how wide the standard width is, and is measured
in points. If standard_num is omitted, the standard width setting
Changing the value of standard_num changes the width of all columns
except those that have been set to a custom value.
If any of the argument settings conflict, such as when standard is TRUE
and type_num is 3, Microsoft Excel uses the type_num argument and
ignores any arguments that conflict with type_num.
If you are recording a macro while using a mouse and you change column
widths by dragging the column border, Microsoft Excel records the
references of the columns using R1C1-style references in the form of
DDS Data Extraction
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
Sent: Tuesday, May 11, 2010 5:54 AM
Subject: Autosize Excel Columns from SAS
I am using SAS (v9.1 on Windows) to write a series of datasets to a
Microsoft Excel workbook with PROC EXPORT. Each dataset goes to a
separate worksheet and currently there are 24. This is working fine.
The thing I don't like is that my Excel file does not size the columns
to fully display the data. I don't want to go in manually and size the
columns. Years ago I know I wrote a VBA macro to do this, which I
called from SAS with DDE.
I am wondering if there is a better way to issue commands from SAS that
will open the Excel file and autosize the columns in ALL worksheets in
the workbook? Or is VBA macro and DDE the preferred method? Any
suggestions are appreciated.
Senior Research Analyst
University of Maryland, Baltimore County
Sondheim Hall, 3rd Floor
1000 Hilltop Circle
Baltimore, MD 21250
Please consider the environment before printing this email and/or any
Confidentiality Notice: This e-mail may contain information that is
legally privileged and that is intended only for the use of the
addressee(s) named above. If you are not the intended recipient, you are
hereby notified that any disclosure, copying of this e-mail,
distribution, or action taken in reliance on the contents of this e-mail
and/or documents attributed to this e-mail is strictly prohibited. If
you have received this information in error, please notify the sender
immediately by phone and delete this entire e-mail. Thank you.