NAME

Win32::Excel::Refresh - Perl extension for automating the refresh of Microsoft Excel Workbooks

SYNOPSIS

  use Win32::Excel::Refresh;

  my $filename = "book1.xls";
  XLRefresh( $filename,
    {
      pivot-tables => 1 ,
	 query-tables => 1,
	 all => 1 ,
	 macros => [ "macro1", "macro2" ] ,
      visible => TRUE 
    }
  );

ABSTRACT

Automate the refresh of Microsoft Excel workbooks.

DESCRIPTION

Win32::Excel::Refresh allows for programatic and/or automatic refreshing of Excel workbooks. This module was written for situations where Excel workbooks are in need of refreshing but the responsible person is too lazy, forgetful or sick of opening up workbooks to execute a few refresh commands and saving the resulting workbook. Complete automation can be acheived by wrapping this module into a script and scheduling in the Windows Task Scheduler (Win32::TaskScheduler), AT(Schedule::At) or a similar cron-type mechanism (Schedule::Cron).

The author uses this modules to keep hundreds of Excel workbooks up-to-date. Each of the workborks are dependent upon data from either web queries or database queries. In most cases, the data is contained in Pivot Tables. While I debated coding this in Visual Basic, I wanted to be able to some advantage of Perl.

A single subroutine, XLRefresh is exported into he callers namespace. This functions takes all a filename and a hash of parameters and does all the work invisibly in the background.

PREREQUISITES

Microsoft Windows

Microsoft Excel

This module is dependent on Win32::OLE and is therefore non-functional on *NIX variants. It has been used successfully with Microsoft Windows 2000 and XP and Microsoft Excel 2000 and 2003.

METHODS

XLRefresh

XLRefresh( $filename, $opts );

$filename is a filename that can be fully specified or relative to the caller's working directory. If relative, the file is first converted to its fully specified form using "rel2abs" in File::Spec::Functions. Filenames may contain forward or back slashes.

$opts is a reference to a hash of parameters used to control the refreshing. Valid parameters are:

   all          => 0|1  Refresh everything
   query-tables => 0|1  Refresh query tables only
   pivot-tables => 0|1	Refresh pivot tables only
   visible      => 0|1  Perform the refreshes visibly or in the background, default: invisible.
   macros       => [ "macro1", "macro2" ] List of macros available to the workbook to run
	

_refresh( $item, $method )

Internal method to invoke a VBA method on a given item. This method should not be called directly.

_refresh_all

Internal method to invoke a method on each item in a collection. This method should not be called directly.

EXPORT

XLRefresh by default

EXTRAS

XLRefresh.pl

XLRefresh.pl is script to execute XLRefresh from the command line. It relies on Getopt::Mixed for the setting of the $opts parameters. It can be found in the script directory.

Usage: XLRefresh -[aqpv] -m macro(s) filename
options:
  -a, --all          Refresh All PivotTables and Queries
  -q, --query-tables Refreshes All QueryTables  
  -p, --pivot-tables Refresh All PivotTables
  -m, --macros       Runs specified macros
  -v, --visible      Shows application while running, defaul invisible

XLRefresh.exe

XLRefresh.exe is a compiled version of the above script using ActiveState's PerlApp Perl. The application was compiled on Windows2000 and has been tested used successfully on WindowsXP. It can be found in the bin directory of this distribution.

EXAMPLES

Examples using Win32::Task Scheduler, AT and Schedule::Cron ...

TODO and possible modifications

+ Add support for charts
+ Add routine to remove old pivot table items cf http://www.contextures.com/xlPivot04.html
+ Add PPM package
+ Complete examples section
+ Support for workbook versioning (?)
+ Allow arguments for macros
+ Validate that the defaults are working correctly

SEE ALSO

Win32::OLE, "rel2abs" in File::Spec::Functions, Getopt::Mixed, Win32::TaskScheduler, Schedule::At, Schedule::Cron, Perl

AUTHOR

Christopher Brown, <ctbrown{at}cpan.org>

COPYRIGHT AND LICENSE

Copyright 2005 by Christopher Brown. This program is free software; you can redistribute it and/or modify it under the same terms as Perl. There software comes with no warranty either expressed or implied.