Date: Mon, 9 Jun 2008 13:48:55 -0700
Reply-To: "Duell, Bob" <BD9439@ATT.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: "Duell, Bob" <BD9439@ATT.COM>
Subject: Excel workbook automation using SAS
Content-Type: text/plain; charset="us-ascii"
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.