I've worked on a program to export VBA code from Excel spreadsheets to STDOUT.
It's worked well for many years, but I needed to start running it on more PCs.

ISSUE 1:

I've encountered runtime errors on about a quarter of the 18 PCs I tried running it on:

    OLE exception from "Microsoft Excel":

    Programmatic access to Visual Basic Project is not trusted

    Win32::OLE(0.1712) error 0x80020009: "Exception occurred"
        in METHOD/PROPERTYGET "VBProject" at Vba2Asc_simple.pl line 24.
    OLE exception from "Microsoft Excel":

    Programmatic access to Visual Basic Project is not trusted

    Win32::OLE(0.1712) error 0x80020009: "Exception occurred"
        in PROPERTYPUT "VBProject" at Vba2Asc_simple.pl line 24.
    OLE exception from "Microsoft Excel":

    Programmatic access to Visual Basic Project is not trusted

    Win32::OLE(0.1712) error 0x80020009: "Exception occurred"
        in METHOD/PROPERTYGET "VBProject" at Vba2Asc_simple.pl line 24.
    Can't use an undefined value as a HASH reference at Vba2Asc_simple.pl line 24.

The PCs were a mix of XP and Win7, all with Excel installed. There were successes
and failures on both XP and Win7. I've tried with ActivePerl 5.8.8, 5.10 and 5.20.

ISSUE 2:

Another problem ... whether the program ran successfully or not, an Excel process
was orphaned upon exit. I was having to keep track of Excel process IDs and kill
the Excel process created by execution of the program. When the program errors,
there is no chance to kill the Excel process just created.

Source code:

#!/usr/bin/perl -w
use strict;
use warnings;

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

use constant TRUE  => 1;
use constant FALSE => 0;

my $fname = shift @ARGV;
my $numlines;
my $sheetname;
my $vb_obj;
my $wb_obj;
my $xl_obj;

$xl_obj = Win32::OLE->new('Excel.Application');
$wb_obj = $xl_obj->Workbooks->Add($fname);

for my $source ( in($wb_obj->{VBProject}->{VBComponents}) )
{
  $sheetname = $source->Name;
  $vb_obj    = $source->{CodeModule};

  $numlines  = $vb_obj->{CountOfLines};
  if ( $numlines )
  {
    my @LINES = $vb_obj->Lines(1, $numlines);

    print '='x78, "\n";
    print "$sheetname\.bas ($numlines lines)\n";
    print '='x78, "\n";

    my @LINES2 = split /\n/, $LINES[0];
    foreach ( @LINES2 )
    {
      s/\x0D//g;
      chomp;
      print "$_\n";
    }

    print "\n";
  } # end if $numlines

  undef $vb_obj;
} # next my $source

undef $wb_obj;
undef $xl_obj;
[download]

The error message points to something in line 24:

  for my $source ( in($wb_obj->{VBProject}->{VBComponents}) )

Encountered errors in FETCH and STORE routines in package Win32::OLE::Tie of Lite.pm:

  Win32::OLE operating in debugging mode: _Unique => 1

  Can't use an undefined value as a HASH reference at 

But why it works on some PCs and not others, hoping someone has an explanation and a solution.

Thanks.