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 (June 2000, week 3)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
Date:         Tue, 20 Jun 2000 13:34:40 -0400
Reply-To:     "Brucken, Nancy" <Nancy.Brucken@WL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Brucken, Nancy" <Nancy.Brucken@WL.COM>
Subject:      Re: EXCEL DDE Commands
Comments: To: Stan Wheeler <swheeler@MY-DEJA.COM>
Content-Type: text/plain; charset="iso-8859-1"

Hi Stan, We've done some similar things here. To open your worksheet without having to hardcode the sheet name into your PUT statement, try something like:

put %unquote(%str(%'[OPEN("&wrksheet")]%'));

Then, the following Excel VBA macro will unprotect the active worksheet:

Sub UnProtectSheet() ' ' UnProtectSheet Macro ' Used to unprotect the active sheet only ' ActiveSheet.Unprotect End Sub

You can call it from within SAS using the following PUT statement, once you've activated the worksheet: put %unquote(%str(%'[RUN("PERSONAL.XLS!UnProtectSheet")]%'));

Substitute for Personal.xls the name of the workbook containing the macro.

The following Excel macro will then unhide all of the columns in a worksheet (we don't have one for rows, but the logic should be similar):

Sub UnhideColumns() ' ' UnhideColumns Macro ' ' This macro unhides any hidden columns in the current sheet. ' Rows("1:1").Select Selection.EntireColumn.Hidden = False

End Sub

You can call this one from within SAS using the following PUT statement: put %unquote(%str(%'[RUN("PERSONAL.XLS!UnhideColumns")]%'));

Hope this helps, Nancy

Nancy Brucken Parke-Davis, Clinical Informatics (734) 622-5767 E-mail address:

-----Original Message----- From: Stan Wheeler [mailto:swheeler@MY-DEJA.COM] Sent: Tuesday, June 20, 2000 11:02 AM To: SAS-L@LISTSERV.UGA.EDU Subject: EXCEL DDE Commands

I could really use some help with these problems:

I have a set of identical workbooks, say F1, F2, etc. from which I want to read data into SAS. Since the workbooks are identical. Each book contains pages named QC, V1 and V2 that contain different types of data.

I'd like to write a macro containing a loop that: opens each workbook, unprotects each page, unhides certain rows and columns and reads the data.

- I can open workbook F1 using the command PUT '[OPEN("F1")]' in a data step but haven't been able to figure out a way to replace the hardcoded "F1" with a macro variable. (This isn't tragic but would make life a little easier.)

- Haven't been able to figure out how to unprotect page QC and to unhide columns G throuh H and rows 10,11 and 12. I've tried using the column.width function to unhide the columns but haven't been able to make it work.

(Perhaps I'm doing something else wrong but I need to unhide the rows and columns because DDE doesn't seem to read from hidden rows or columns.)

Thanks for any suggestions,

Stan Wheeler

Sent via Before you buy.

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