Date: Tue, 10 Jun 2008 11:59:24 -0700
Reply-To: "Duell, Bob" <BD9439@ATT.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: "Duell, Bob" <BD9439@ATT.COM>
Subject: Re: Excel workbook automation using SAS
In-Reply-To: <010f01c8cb18$5fadd2e0$832fa8c0@HP82083701405>
Content-Type: text/plain; charset="us-ascii"
Oh. That was easy. I've got a basic script file named
"Camp_refresh.vbs" that does exactly what I want:
=================================================
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook =
objExcel.Workbooks.Open("\\mypc\rptbase\Campaigns_@_YYYYMMDD.xls")
Set objWorksheet = objWorkbook.Worksheets(1)
objWorkbook.Sheets("Performance To Date").Select
objWorkbook.ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
objWorkbook.Sheets("Region by Week").Select
objWorkbook.ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh
objWorkbook.Sheets("User by BA").Select
objWorkbook.ActiveSheet.PivotTables("PivotTable3").PivotCache.Refresh
Dim strNOW
strNOW = Now
Dim strYMD
strYMD = DatePart("yyyy",strNOW)
strYMD = strYMD & Right(100+DatePart("m",strNOW),2)
strYMD = strYMD & Right(100+DatePart("d",strNOW),2)
objWorkbook.SaveAs Filename:= _
""\\fserver\plumbers\Campaigns_@_" & strYMD & ".xls"" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
objExcel.Quit
=================================================
Executing the script from SAS is as simple as rc =
system('c:\mydir\Camp_refresh.vbs') in a data null step.
I'm sure there's much more to learn, but this is a great start. I guess
I'll have to add Visual Basic to my resume!
Thanks ever so much!
Bob
________________________________
From: Mary [mailto:mlhoward@avalon.net]
Sent: Tuesday, June 10, 2008 9:22 AM
To: Duell, Bob; SAS-L@LISTSERV.UGA.EDU
Subject: Re: Re: Excel workbook automation using SAS
Bob,
Creating an Excel Macro creates VB script; you can do Tools-record
macro", then click on each sheet and refresh, then do the save as you
want, then stop recording your macro. Then do Tools- Macros- Edit
Macro, and take a look at the code that you have generated. Once
you've got that macro, try to get SAS to execute it. You can also edit
the code in the Visual Basic editor within Excel by going Tools- Macros-
Edit Macro.
It would probably be good to look at a book on Programming within Excel,
and also look at the papers below. The Microsoft Office Excel series by
Thompson Course Technology has macros in its intermediate level and
programming in its advanced level.
Or if you happen to be in the Cedar Rapids, Iowa, area, you can take the
wonderful courses in Excel that I teach at Kirkwood Community College!
-Mary
----- Original Message -----
From: Duell, Bob <mailto:BD9439@ATT.COM>
To: SAS-L@LISTSERV.UGA.EDU
Sent: Tuesday, June 10, 2008 10:51 AM
Subject: Re: Excel workbook automation using SAS
Unfortunately, I cannot set the pivot tables to "refresh on
open". The
point of the exercise is to put the workbooks on a file server
that can
be used by people that do not otherwise have access to the data.
I'm sure this can be done with an Excel VBSCRIPT, but I've never
written
one before. I found similar examples with a Google search, but
nothing
exactly like I need. My last resort will be to ask for help on
a VB
site. Otherwise, I'll have to find a good online tutorial
someplace.
-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On
Behalf Of
Choate, Paul@DDS
Sent: Monday, June 09, 2008 5:04 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: Excel workbook automation using SAS
Bob -
If you can set your pivot tables to "Refresh on Open" then you
just need
SAS to open and close them which can be done with a sysexec and
a little
DDE.
/*open and close */
options noxwait noxsync xmin;
%let file="G:\Refreshed Workbook.xls";
%sysexec &file;
filename xl_out dde 'excel|system' notab;
data _null_;
file xl_out;
put '[save()]';
put '[quit()]';
run;
filename xl_out clear;
The "Refresh on Open" is on the Pivot Tables Options dialogue.
One
additional benefit is that you can then uncheck "Save Data with
Table
Layout" which will decrease your workbook sizes by half or more.
Paul Choate
DDS Data Extraction
(916) 654-2160
-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On
Behalf Of
Mary
Sent: Monday, June 09, 2008 2:16 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: Excel workbook automation using SAS
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
