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.

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.