cacharbe has asked for the wisdom of the Perl Monks concerning the following question:

I am trying to read a directory of files, and if they are excel files (.xls extension) with a particular naming convention, work certain magic(tm) on them. The problem is, if I use regex to determine validity, and combine the path info with the filename info and push it into an array for later use, when I try to open the .xls file in excel, I get the following error:

OLE exception from "Microsoft Excel":
Unable to get the Open property of the Workbooks class
Win32::OLE(0.1101) error 0x800a03ec
in METHOD/PROPERTYGET "Open" at fileopen.pl line XX
So..I simplified my problem down to the following :

Please note that if I create the array like so:

@array = ('somepath', 'someotherpath',...,'somefinalpath');

instead of using the regex method, everything works hunky dory, but as soon as I try to use the values in the array built by the combination of the path info + the filename info. Nada, nothing, no dice.

I'm running Active Perl 522 on Win2k. Ideas?

Thanks again for your help...I'm truly stumped.

Chuck. ------ 
use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';
$Win32::OLE::Warn = 3;  
my $mypath = 'c:\perl\projects';
my $elem = "";
my $filetype = "xls";
my @files;

        if (opendir CURRDIR,  $mypath){
            @allfiles = readdir CURRDIR;
            closedir CURRDIR;
        }
        for my $elem (0..$#allfiles){
            if ($allfiles[$elem] =~ /^[\d\w\s]+\.$filetype/) {
               $cfile = "$mypath\\$allfiles[$elem]";
               push (@files,$cfile);
            }
        }

my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
                          || Win32::OLE->new('Excel.Application', 'Qui
+t');

# @files = ('c:\perl\projects\letterdist.xls', 'c:\perl\projects\freqs
+.xls'; #Use this instead of above regex, below works fine

for my $x (0..$#files){
    printf "%-s\n",$files[$x];
    my $Book = $Excel->Workbooks->Open($files[$x]) || die " $! "; # op
+en
Excel files
    #my $Book = $Excel->Workbooks->Open('c:\perl\projects\LetterDist.x
+ls')
|| die " $! "; # open Excel files #This works fine
    $Book->Close();
}

[download]

Replies are listed 'Best First'.
Re: Using Win32::OLE 'Excel' and stumped
by chromatic (Archbishop) on Apr 30, 2001 at 23:07 UTC
    I'd try something like the following: 
    use File::Spec;

# define variables as above

opendir(CURRDIR, $mypath) or die "Can't open $mypath: $!";
my @files = map { File::Spec->catfile($mypath, $_) }
        grep { /^[\d\w\s]+\.$filetype/ }
        readdir CURRDIR;

foreach my $file (@files) {
    # reveal potentially nasty leading/trailing whitespace
    print "Processing ->$file<-\n";
    my $book = $Excel->Workbooks->Open($file) 
        or die "Error opening $file: $!";
    $book->Close();
}

    [download]
    This lets a tested, cross-platform module File::Spec handle the dirty business, avoids string interpolation that may be trouble, and is slightly more Perlish.
[reply]
[d/l]
      Hrm... Never used File::Spec before. prettier. Still doesn't work, but it is still throwing the same error.

      *shrug*

      I have no idea what is going on. I'm sure it's string interpolation, but I can't find the root of it. Others are mentioning that it is working fine on their systems.

      Arrgh.

      Chuck
[reply]
Re: Using Win32::OLE 'Excel' and stumped
by the_slycer (Chaplain) on Apr 30, 2001 at 21:45 UTC
    Hrm.. I can run this just fine on my W2k box (only changed the path's). Make sure that your path etc is correct, print out what you are adding to the array prior to pushing it in there.
    Also, perhaps just a style thing, but I think that
    foreach $elem (@allfiles){
    if ($elem =~ /[\d\w\s]+\.$filename/){
    #rest of if code
}

    [download]
    looks much nicer and is much easer to read than those for loops, or even better drop the $elem alltogether and use $_.

    Update: If I change the "\\" to "/" (forward slashes should work the same as double-backslashes) I now get the error, otherwise everything still works fine...
[reply]
[d/l]
[select]
      'Print out what I am pushing in'...I did. Path is correct.

      foreach vs. $# *shrug* Used both in this instance. So many ways to get to the same end...

      Chuck.
[reply]
Re: Using Win32::OLE 'Excel' and stumped
by cacharbe (Curate) on May 01, 2001 at 00:29 UTC
    Got it figured out....It was my editor. I'm using DzSoft
    perl editor. Run it from a command line, it now works.

    But I upgraded to 623, so that's nice. *smack* I NEVER
    trust my editor...EVER, and I've been circling this for how long?!?

    *GRUMBLE*GRUMBLE*GRUMBLE*GRUMBLE*

    Chuck.
[reply]
Re: Using Win32::OLE 'Excel' and stumped
by buckaduck (Chaplain) on Apr 30, 2001 at 21:41 UTC
    You haven't shown us the printf output; should we assume that the filenames are being printed out all right?

    Even so, it might have been better to use /\.\Q$filetype\E$/ in the regex rather than /^[\d\w\s]+\.$filetype/, for a variety of reasons. For one thing, it will ensure no nastiness after the ".xls" in the filename. It's a longshot, but barely possible.

    On another note, 

    my $Book = $Excel->Workbooks->Open($files[$x]) or die " $! ";

    [download]
    is much better than 
    my $Book = $Excel->Workbooks->Open($files[$x]) || die " $! ";

    [download]
    in general. But I doubt that it's your problem here.

    buckaduck

[reply]
[d/l]
[select]
      Tried the:
      /\.\Q$filetype\E$/

      To no avail. *sigh* chuck.
[reply]
[d/l]
Re: Using Win32::OLE 'Excel' and stumped
by cacharbe (Curate) on Apr 30, 2001 at 21:47 UTC
    As to the regex, it isn't the regex I'm using in the application, The app's version is much more involved, but this throws the same error, so there you are.

    The 'or' isn't an issue, either, tried it both ways, as well as different variations of forward and backslashes for path info, and "" vs. '' combinations as well.

    As I said...
    Stumped.

    Chuck.
[reply]
Re: Using Win32::OLE 'Excel' and stumped
by Eureka_sg (Monk) on Apr 30, 2001 at 21:31 UTC
    $allfiles[$elem] =~ /^[\d\w\s]+\.$filetype/

    I believe the problem comes from your regex. Its's searching for 1 or more occurences of 'd','\','w' or 's' in the filenames. Note that \d,\w,\s don't work in a character class. As a result, @files is empty.

    $allfiles[$elem] =~ /^(?:\S)+\.$filetype/

    You could replace it with something like the above,which checks for non-whitespace characters.

    Update: Thanks for the lesson..It's 1:35 a.m. here and I'm getting goggy : )..To think that I've read through Mastering Regular Expressions some time back..

[reply]
[d/l]
[select]
      Actually \d,\w and \s do work in character classes, though they are exceptions. This does mean that his regex is not very fussy, accepting any name of one or more characters made up of any combination of numerics, alphanumerics and whitespace, but I don't think it is the source of his problem.

      --
      I'd like to be able to assign to an luser

[reply]

      Actually, they do. Try it.

              - tye (but my friends call me "Tye")
[reply]

Back to Seekers of Perl Wisdom