LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (June 2008, week 2)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
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


Back to: Top of message | Previous page | Main SAS-L page