You're close - very close - but no nicotine patch. You are trying to manipulate the address as a string to get the number of the column, or at least that's how it appears to me. I think you will find it easier to use the column number directly, as shown in the code below:

use strict;
use warnings;
use Win32::OLE;
use Win32::OLE::Const 'Microsoft Excel';

my $xl = Win32::OLE->new('Excel.Application');
$xl->{EnableEvents} = 0;
$xl->{Visible} = 1;

my $wb = $xl->Workbooks->Add;
my $sht = $wb->Sheets(1);
$sht->Cells(3,3)->{Value}="Here";
my $penultimate_column = $sht->UsedRange->SpecialCells(xlCellTypeLastC
+ell)->{Column} - 1;
print $penultimate_column;

[download]

There can be issues with UsedRange if you need to delete rows or columns within the range, but my tests indicate that the issues that exist in VBA don't exist in Perl. That's quite likely, as there are many things that can happen to the used range. See this as a starting point if you want to get all technical. Note that you don't have to use Range($address) with a string address to point to a cell. It's equally valid to use Cells($row, $col) with the row and column as numbers. Some things are far easier this way.

It ought to be even simpler to use UsedRange->Columns->{Count} to give the number of columns, but for some reason I couldn't get that working, possibly because it's gone midnight.

Regards,

John Davies

In reply to Re: Win32 Ole find 2nd to last column in Excel by davies
in thread Win32 Ole find 2nd to last column in Excel by NewMonk2Perl

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.