Date:         Tue, 30 Jan 2001 13:57:40 -0500
Reply-To:     Howard Schreier <Howard_Schreier@ITA.DOC.GOV>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Howard Schreier <Howard_Schreier@ITA.DOC.GOV>
Subject:      Re: Excel 2000 Won't Rename Worksheet

Thanks to Nancy Brucken and Koen Vyverman for confirming that this is a bug
and for suggesting workarounds.

I ended up using a variation of Nancy's approach. First I stored a copy of
her VBA (Visual Basic for Applications) macro in the target workbook. Here's
the VBA code:

   Sub RenamSh()
   '
   ' renamsh Macro
   '
   '
        With Selection
           ActiveSheet.Name = ActiveCell.Value
        End With
   End Sub

Then the following SAS statements writing to the DDE System topic do the
worksheet renaming:

   put '[FORMULA("' "'" wsname +(-1) '")]';
   put '[RUN("bookname.xls!renamsh")]';
   put '[CLEAR(3)]';

WSNAME is a character variable containing the name to be used. So the first
PUT stores this in the active cell. I always do this right after inserting a
new worksheet, so there's never a problem of overwriting data.

The quotation marks are sequenced rather intricately, and the details are
critical. Suppose the current value of WSNAME is "whatsit"; here's what
Excel receives:

   [FORMULA("'whatsit")]

The leading single quote is important because I have some names which happen
to consist of numeric digits only, and this forces Excel to treat such
values as character strings rather than as numbers.

The second command invokes the macro and the third erases the name from the
active cell.

By the way, Koen is giving a presentation at SUGI which sounds like it will
be most worthwhile for those of us dealing with the numerous messy details
of SAS-Excel communication and automation.

On Wed, 24 Jan 2001 12:04:39 -0500, Howard Schreier
<Howard_Schreier@ITA.DOC.GOV> wrote:

>This code should cause Excel to rename worksheet "Sheet2" in the active
>workbook:
>
>   filename commands dde 'excel|system';
>   data _null_;
>   file commands;
>   put '[WORKBOOK.NAME("Sheet2","Some New Name")]';
>   run;
>
>It works as expected when Excel 5 is running.
>
>With Excel 2000 I get:
>
>   ERROR: DDE session not ready.
>   FATAL: Unrecoverable I/O error detected in the execution of the
>          data step program.
>          Aborted during the EXECUTION phase.
>
>There *is* a Sheet2 in the active workbook when this happens.
>
>Also I can see that Excel has at least begun to respond. The *name* of the
>active worksheet (not necessarily Sheet2) is selected in its tab. In other
>words, if I switch over to Excel, I can immediately begin typing in a new
>name for that sheet.
>
>Any theories or workarounds?
>
>TIA