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:  Nancy.Brucken@wl.com


-----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 Deja.com http://www.deja.com/
Before you buy.