File Coverage

blib/lib/Win32/Excel/Refresh.pm

Criterion	Covered	Total	%
statement	19	21	90.4
branch			n/a
condition			n/a
subroutine	7	7	100.0
pod			n/a
total	26	28	92.8

package Win32::Excel::Refresh;

use 5.006;
use strict;
use warnings;
use Carp;

require Exporter;
use AutoLoader qw(AUTOLOAD);

use File::Spec::Functions ':ALL';

use Win32::OLE;
use Win32::OLE qw(in);
use Win32::OLE::Const 'Microsoft Excel';

# use Data::Dumper;

our @ISA = qw(Exporter);

# Items to export into callers namespace by default. Note: do not export
# names by default without a very good reason. Use EXPORT_OK instead.
# Do not simply export all your public functions/methods/constants.

# This allows declaration	use Win32::Excel::Refresh ':all';
# If you do not need this, moving things directly into @EXPORT or @EXPORT_OK
# will save memory.
our %EXPORT_TAGS = ( 'all' => [ qw() ] );
our @EXPORT_OK = ( @{ $EXPORT_TAGS{'all'} } );
our @EXPORT = qw(XLRefresh);

our $VERSION = '0.02';


# Preloaded methods go here.

# ---------------------------------------------------------------
# SUBROUTINE: XLRefresh
#   Usage: XLRefresh( $filename, $opts );
#
#   $filename is a relative or absoulte filename.
#
#   $opts is a hash reference of parameters
#	 { all          => [ 0 | 1 ] }
#     { query-tables => [ 0 | 1 ] }
#     { pivot-tables => [ 0 | 1 ] }
#     { visible      => [ 0 | 1 ] }
#     { macros      => [list] }
#
#	list is string of macros and argumants, "macro(args)", "marco(args)"
#	e.g. macros => "RefreshAllPivotTables( true, true )",
#
#  2) Implement more of a Excel Feel
#      XLRefresh.exe -m Sheet1!RefreshAllPivotTables( true, true );
# ---------------------------------------------------------------
sub XLRefresh {

	my $filename = shift || die("No filename supplied");
	my $opts = shift;

  	# print Dumper( $opts );

  # -------------------------------------------------------------
  # CLEAN AND TRAP FILENAME INPUT
  # -------------------------------------------------------------
	$filename = rel2abs($filename);
	croak("$filename does not exist.\n") if ( !-e $filename);

  # -------------------------------------------------------------
  # OPEN A NEW APPLICATION INSTANCE.
  #   Opening a new application instance prevents a workbook of
  #   same name and a generation of an error and prevents the the
  #   decision as to whether to close the existing application or
  #   not.  Cf the deprecated subroutines at the end to see how
  #   it was done previously.
  # -------------------------------------------------------------
	my $Excel;
	$Excel = Win32::OLE->new('Excel.Application', 'Quit');

  # -------------------------------------------------------------
  # Set the visibility of the operations
  # -------------------------------------------------------------
	$Excel->{DisplayAlerts} = "False";
	$Excel->{Visible} = $opts->{'visible'} || 0 ;         # if you want to see what's going on


  # -------------------------------------------------------------
  # OPEN FILE:
  # There is no need to trap if the workbook is open.  If the file is open elsewhere
  # do not save this file.
  # The open function:
  # expression.Open(FileName, RefreshLinks, ReadOnly, Format, Password, WriteResPassword,
  # IgnoreReadOnlyRecommended, Origin, Delimiter, Editable, Notify, Converter, AddToMRU)
  # -------------------------------------------------------------
	my $wb = $Excel->Workbooks->Open( $filename );      # open the file

  # -------------------------------------------------------------
  # Workbook.RefreshAll
  # Refreshes all external data ranges and PivotTable reports in the specified workbook.
  # -------------------------------------------------------------
	$wb->RefreshAll if ( $opts->{all} );

  # -------------------------------------------------------------
  # Refresh Charts
  # -------------------------------------------------------------
	#_refreshall( $wb, "Charts", "Refresh" );
	# foreach my $chart ( in($Excel->Charts) ) {
	#	$chart->Calculate;
	#}


  # -------------------------------------------------------------
  # REFRESH: Query Tables and Pivot tables
  #  Iterate through worksheets
  # -------------------------------------------------------------
	foreach my $ws ( in( $wb->WorkSheets ) ) {

		# print "Updating Worksheet: $ws->{Name}\n";

	   # ------------------------------------------------------
	   # Query Tables
	   # ------------------------------------------------------
		if ( $opts->{'query-tables'} ){
			_refreshall( $ws, "QueryTables", "Refresh");
			# print "\tRefreshing QueryTable(s)\n";
		}

	   # ------------------------------------------------------
	   # Pivot Tables
	   # ------------------------------------------------------
		if ( $opts->{'pivot-tables'} ) {
			_refreshall( $ws, "PivotTables", "RefreshTable")	;
			# print "\tRefreshing Pivot Table(s)\n";
		}

	}


  # -------------------------------------------------------------
  # Run Macros: Query Tables and Pivot tables
  # -------------------------------------------------------------
	foreach my $macro ( @{ $opts->{'macros'} } ) {
		$Excel->Run( $macro );
		# $Excel->Run('Sheet1.macro1');
		# print "Running Macro: $macro\n";
	}

  # -------------------------------------------------------------
  # SAVE WORKBOOK
  # expression.SaveAs(Filename, FileFormat, Password, WriteResPassword,
  #	ReadOnlyRecommended, CreateBackup, AccessMode, ConflictResolution,
  #	AddToMru, TextCodePage, TextVisualLayout)
  # -------------------------------------------------------------
	## Recalculate before closing
	$Excel->Calculate;
	$wb->Save;
	$wb->Close;

	# $wb->SaveAs(
	#	{ 	Filename 	=>$filename,
	#		AddToMru => 'FALSE' ,
	#	}
	#);

	$Excel->Quit(); #  if ( $close_on_exit == TRUE );

} ##  END SUBROUTINE XLRefresh


## SUBROUTINE _refresh, _refreshall
## $self->_refreshall( $obj, collection_method, individual_method );
##	e.g. $self->refreshall($app, "workbooks", "refresh");
## 	Generic method for refreshing object.
## 	object: A suitable Win32::Object
##	collection_method: Method for returning an array of individuals in a collection
##	individual_method: Method to be executed for each individual
sub _refreshall {

	my $obj = shift;
	my $collection_method = shift;
	my $individual_method = shift;

	return if ( $obj->$collection_method->Count < 1 );

	print "\tUpdating $collection_method: $obj->{Name}\n";

	foreach my $individual ( in( $obj->$collection_method ) ) {
		$individual->$individual_method;
	}

	## Error Trap.
	if ( Win32::OLE->LastError() ) {
		print "TRAPPING ERROR\n";
		print "Win32::OLE->LastError()\n";
	}

} # END SUBROUTINE _refresh_all


sub _refresh {

	my $individual = shift;
	my $individual_method = shift;

	$individual->individual_method;

} # END SUBROUTINE _refresh




# Autoload methods go after =cut, and are processed by the autosplit program.

1;
__END__