Exporting VBA code from Excel: Win32::OLE exceptions & orphaned processes

[shockers_jm]

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;

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.

[Anonymous Monk]

ISSUE 1:

And off to search MSDN you go :) Programmatic access to Office VBA project is denied, Programmatic access to Visual Basic Project is not trusted

ISSUE 2: When the program errors, there is no chance to kill the Excel process just created.

eval or OLE die handler or both?

my $excel = MakeItStart();
eval {
  DoStuff( $excel );
  1;
} or do {
    warn "UHOH $@ ";
    KillIt( $excel );
};
exit( 0 );

[shockers_jm]

Thank you, thank you, thank you Anonymous!

Issue 1 resolved, for Excel 2007 on Win7:

File
-> Options
-> Trust Center
-> Trust Center Settings
-> Macro Settings
-> (x) Trust access to the VBA project object model


I'm sure it's something similar for XP.

For Issue 2, eval() huh? Interesting.

The $excel should be

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

and most everthing else is the DoStuff to be eval'd.

I'll play around with that.

I still have the brute-force before/after process list trick to fall back on.

Mucho thanks.

[Anonymous Monk]

Typical Microsoft ... require you to "trust" access to a facility that has been part of their setup for years. But, provide no way to "trust" access by a particular external script.