The Perl and Raku Conference 2025: Greenville, South Carolina - June 27-29 Learn more

CTBROWN / Win32-Excel-Refresh-0.02 / extras / XLRefresh.pl 

            

              
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87

              
# --------------------------------------------------------
# XLRefresh.pl
#
#  Perl program to automatically refresh Excel workbooks through a command-line interface
#
#  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
#
# Compiled with PerlApp from ActiveState
# PerlApp --clean -exe XLRefresh.exe --icon ..\images\xlr.ico XLRefresh.pl
#
# Copyright 2005. Christopher Brown.  CTBROWN@CPAN.ORG
# --------------------------------------------------------
# --------------------------------------------------------
# USE NECESSARY MODULES
# --------------------------------------------------------
use Win32::Excel::Refresh;
use strict;
our ( $opt_a, $opt_p, $opt_q, $opt_m, $opt_v ) ;
use Getopt::Mixed;
Getopt::Mixed::init("a p q v m=s all>a pivot-tables>p query-tables>q macros>m visible>v");
Getopt::Mixed::getOptions();
use File::Spec::Functions ':ALL';
# --------------------------------------------------------
# TRAP ERRORS: Usage
# --------------------------------------------------------
# my $filename = join( ' ', @ARGV );
if ( ! @ARGV ) {
        print <<'EOT';
XLRefresh
  Perl program to automatically refresh Excel workbooks through a command-line interface
  Usage: XLRefresh -[aqpv] -[m macro(s)] filename(s)
  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
EOT
        exit;
}
# --------------------------------------------------------
# LOOP THROUGH FILES
# --------------------------------------------------------
foreach my $filename ( @ARGV ) {
        # --------------------------------------------------------
        # TRAP ERRORS: Invalid file(s)
        # --------------------------------------------------------
        # print $filename;
        $filename = rel2abs($filename);
        die("$filename does not exist.\n") if ( !-e $filename);
        ## CREATE $options
        my @macros = split(';', $opt_m);
        my $opts = {
                'all'          => $opt_a ,
                'query-tables' => $opt_q ,
                'pivot-tables' => $opt_p ,
                'macros'       => \@macros,
                'visible'            => $opt_v
        };
        &XLRefresh($filename, $opts);
}