Date:         Mon, 9 Jun 2008 16:16:04 -0500
Reply-To:     Mary <mlhoward@avalon.net>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Mary <mlhoward@AVALON.NET>
Subject:      Re: Excel workbook automation using SAS
Comments: To: "Duell, Bob" <BD9439@ATT.COM>
Content-Type: text/plain; charset="iso-8859-1"

Bob,

I haven't ever tried it but apparently SAS can run an Excel Macro- since SAS does not support Excel 2007, I assume that this would have to be for Excel 2003.  Here's a note with references that I got a while back:

Mary

SAS can, indeed, run a version4 Excel Macro and there are a number of
papers that show examples. I just did a quick googling and found the
following

http://www2.sas.com/proceedings/sugi30/089-30.pdf


Koen Vyverman has several excellent papers including one with his
hench-DDEr, Bill Viergever

http://www2.sas.com/proceedings/sugi28/016-28.pdf

This last paper shows using macros with Word.  Koen went to work for SAS a
few years ago so we no longer see his sage advice on the L.

Good luck.

Nat

Nat Wooding
Environmental Specialist III
Dominion, Environmental Biology
4111 Castlewood Rd
Richmond, VA 23234
Phone:804-271-5313, Fax: 804-271-2977

-Mary
  ----- Original Message ----- 
  From: Duell, Bob 
  To: SAS-L@LISTSERV.UGA.EDU 
  Sent: Monday, June 09, 2008 3:48 PM
  Subject: Excel workbook automation using SAS


  Hi,

  I want to automate a very tedious task using SAS and will be very
  grateful for any help.  Here's what I want to do.

  I have several Excel 2003 "master" workbooks, each of which contain
  multiple spreadsheets that are made up with pivot tables or links to
  external data sources.  Each week, I open each workbook and "refresh"
  selected sheets and then save a copy with a new name (imbedding a date
  into the file name).

  For example, I have a workbook named "Weekly_Summary.xls".  After
  refreshing four of the five sheets inside, (the fifth contains static
  content), I same the result to a new workbook named
  "Weekly_Summary_20080609.xls", ZIP-compress the file and copy the result
  to a file server.

  Does anyone have any sample code that will do this using SAS?  I don't
  need any help with the file compress or copy-to-server parts, but I
  don't know how to use SAS to open a workbook, execute the "refresh"
  commands on selected sheets, and save the result to a new file name.

  It can't be that hard, but I don't know how to get started.  If the
  answer is "use a VB script", a simple example would be appreciated.

  Thanks!

  Bob