LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (May 2010, week 2)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
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)
Comments: To: Jack Clark <jclark@hilltop.umbc.edu>
In-Reply-To:  <456B52C41B724C41B96561D7AD283E7D01F04D04@mail.chpdm.umbc.edu>
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.

Paul Choate DDS Data Extraction (916) 654-2160 -----Original Message----- From: Jack Clark [mailto:jclark@hilltop.umbc.edu] 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 ; data _null_; file ddecmds; * 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; put '[workbook.next()]'; end; *put '[workbook.activate(worksheet(1))]'; put '[save]'; put '[error(false)]'; put '[quit()]'; run;

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.

Jack

Jack Clark Senior Research Analyst phone: 410-455-6256 fax: 410-455-6850 jclark@hilltop.umbc.edu -----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: put '[COLUMN.WIDTH(1,"C1:C7",TRUE,3,TRUE)]';

http://www.google.com/search?q=SAS+DDE++COLUMN.WIDTH+filetype%3Apdf

from Macrofun.hlp:

/* 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 reference.

Syntax 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 width.

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 "Database". 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 Format menu.

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 each column

Standard_num - specifies how wide the standard width is, and is measured in points. If standard_num is omitted, the standard width setting remains unchanged.

Remarks 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 text. */

Paul Choate DDS Data Extraction (916) 654-2160

-----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Jack Clark Sent: Tuesday, May 11, 2010 5:54 AM To: SAS-L@LISTSERV.UGA.EDU Subject: Autosize Excel Columns from SAS

Hello,

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.

Jack

Jack Clark Senior Research Analyst phone: 410-455-6256 fax: 410-455-6850 jclark@hilltop.umbc.edu

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 attachments.

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.


Back to: Top of message | Previous page | Main SAS-L page