Date:         Tue, 23 May 2000 10:01:07 -0700
Reply-To:     "William W. Viergever" <wwvierg@IBM.NET>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "William W. Viergever" <wwvierg@IBM.NET>
Subject:      Re: Reading Excel via DDE: what if you don't know the names of
              the worksheets in advance
Comments: To: Nathaniel_Wooding@DOM.COM
In-Reply-To:  <852568E8.0046BC49.00@smtpmta.vapower.com>
Content-Type: text/plain; charset="us-ascii"

Hi Nat:

The only thing that comes to mind would be to create a VBA function/macro and have that stored in your PERSONAL.XLS (M$ Excel autoexec-like XLS file that gets opened for every Excel session). Then, using the DDE-system commands, run that macro. I'm not sure, but you may also be able to "run" the VBA macro as a command-line option to invoking Excel in the first place (don't know if you can do that via DDE).

The VBA macro would loop through the worksheets "collection" and then create some predetermined-named worksheet (e.g. DIR_SHEETS), and write out the worksheet names to that new sheet (I may the order of these tasks backwards <g>: create sheet, loop, then write out the names). From there you could do your usual DDE stuff, read the worksheet names (ignoring DIR_SHEETS <g>) and define your required DDE LIBNAMEs accordingly.

HTH

Late


At 08:56 AM 5/23/2000, Nat Wooding wrote:
>I have a situation in which I will be receiving EXCEL files with worksheet names
>that I will not be able to anticipate. I could, of course, open the spreadsheet
>and
>record the names of the sheets manually and then type them into my SAS job.  I'm
>lazy so I would like to be able to automate this in the following general
>fashion:
>
>1)Have SAS grab the names of the worksheets
>2) generate a series of macro calls to read each of these individually
>
>Has anyone had any luck in sucking the names of the sheets out of an Excel
>workbook? The rest of the DDE is no problem.
>
>Thanks
>
>Nat Wooding
>Virginia Power

----------------------------------------------------------------------------
William W. Viergever                      Voice  : (916) 483-8398
Viergever & Associates                    Fax    : (916) 483-8399
A SAS Institute Quality Partner (USA)     E-mail : wwvierg@ibm.net
Sacramento, CA 95825

"The fastest way to succeed is to double your failure rate."
- Thomas J. Watson, Sr. - Founder of IBM

"The large print giveth, and the small print taketh away."
- Tom Waits
----------------------------------------------------------------------------