TIMTOWTDI, in Excel as in Perl. Excel usually has two ways of doing anything. What gets my goat is that those two ways are usually utterly incompatible.

When processing Excel files with Win32::OLE, I always create an instance of Excel in my Perl code, and strongly recommend this practice. Unfortunately, this does not stop Excel opening files itself before any files the user designates - add-ins would fall into this category. This means that the Perl instance of Excel may have several files open.

Excel's next quirk is that the filename it holds in the VBProjects collection may well be different from the filename given by the user. There are three main causes of differences. The first is that the user may not have specified a directory. Excel will add a fully qualified path. The second is that the user may not have specified an extension. He knows, after all, that the file has no extension. Windows tells him this. No-one has mentioned the option to hide extensions for registered file types, which means that he never sees ".xls". The third is that he may be using a mapped network drive. This Excel will convert from the mapped name to an unmapped, fully qualified name.

The following code is my attempt to compare two file names in these different formats. It works for everything I've tried, but I'm sure that there is a better way, probably involving regexes.

use strict;
use warnings;

my $f1 = 'z:\proj\proj';
my $f2 = '\\192.168.1.3\server\proj\proj.ext';
my $s1 = substring($f1);
my $s2 = substring($f2);
if ($s1 eq $s2) {
    print "Matched $s1\n";
} else {
    print "Main bus B undervolt - $s1 <> $s2\n";
}

sub substring {
    my $file = shift;
    my $back = rindex($file, "\\");
    my $dot = rindex($file, ".");
    if ($dot > $back) {
        return substr($file, $back + 1, $dot -$back - 1);
    } else {
        return substr($file, -(length($file) -1 -$back));
    }
}

[download]

Any advice would be appreciated, especially pointers to further reading. The intention of the code is to take everything after the last backslash (if there is one) up to the last dot (if there is one after the last backslash) and to compare this for each string.

TIA & regards,

John Davies

Update: a /msg has pointed out (correctly AFAICT without more extensive testing) that launching Excel with the /s option prevents loading of add-ins and personal.xls. There are other ways of loading files, such as VBA references, that I haven't tested. My fear on this is that there are some ways to drag files into an instance somewhere in the depths of Excel (possibly versions yet to be released) that I haven't met.

In reply to Inelegant code to compare file names by davies

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • 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! —
  • 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:
    & &amp;
    < &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.