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

Good Afternoon Monks, how would you find the 2nd to last column in an excel spreadsheet using Win32 Ole? I found this code to get the last column but how do I subtract 1 from it to get the cell I want? 
my $last_cell = $Sht1->Range("A1")->EntireColumn->
    SpecialCells(xlCellTypeLastCell)->{Address};  
    
(my $last_col, my $last_row) = $last_cell =~ /\$(\w+)\$(\d+)/;

[download]
Thanks in advance. Paul

Replies are listed 'Best First'.
Re: Win32 Ole find 2nd to last column in Excel
by davies (Monsignor) on Mar 17, 2011 at 00:23 UTC

    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

[reply]
[d/l]
[select]
      Thanks, that works for what I want to do!
[reply]

Back to Seekers of Perl Wisdom