Date: Mon, 9 Jun 2008 16:16:04 -0500
Reply-To: Mary <firstname.lastname@example.org>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Mary <mlhoward@AVALON.NET>
Subject: Re: Excel workbook automation using SAS
Content-Type: text/plain; charset="iso-8859-1"
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:
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
Koen Vyverman has several excellent papers including one with his
hench-DDEr, Bill Viergever
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.
Environmental Specialist III
Dominion, Environmental Biology
4111 Castlewood Rd
Richmond, VA 23234
Phone:804-271-5313, Fax: 804-271-2977
----- Original Message -----
From: Duell, Bob
Sent: Monday, June 09, 2008 3:48 PM
Subject: Excel workbook automation using SAS
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.