Perl Win32::OLE Excel Selection + TextToColumns

Finally figured out how Excel Selection + TextToColumns translates from VB to Perl using the module Win32::OLE

One of those things that I’m sure millions of have done, but I never saw an exact example before so I never could get it to work before.

One other thing that has been a huge help: Excel Constant Enumerations

Hopefully these will save someone else some hassle, good luck!

#!c:/perl/bin/perl.exe

use strict;
use warnings;
use Win32::OLE;

my $excel        = Win32::OLE->new( 'Excel.Application' )
  or die "Could Not Start Excel.\n";
$excel->{ 'Visible' }           = 1;
$excel->{ DisplayAlerts }       = 0;
$excel->{ SheetsInNewWorkBook } = 1;

my $workbook = $excel->Workbooks->Add();
my $sheet = $workbook->Worksheets( 1 );
$sheet->{ 'Name' } = 'Approved Vs Denied';
$sheet->Activate;
$sheet->Range( "A1" )->{ Value } = '"1","2","3"';
$sheet->Range( "A2" )->{ Value } = '"4","5","6"';

$sheet->Range( "A1:A2" )->Select(); # Make your cell selections
$excel->Selection->TextToColumns( {  #But then reference the selection using the Excel Object
        Destination          => $sheet->Range( "A1" ), # Where to put the top left corner of the text-to-columns
        DataType             => 1,                        # Fixed ( 2 ) // Delimited ( 1 )
        TextQualifier        => 1,                        # DoubleQuote ( 1 ) // None (-4142) // SingleQuote( 2 )
        ConsecutiveDelimiter => 0,                        # (True or False)
        Tab                  => 0,                        # (True or False)
        Semicolon            => 0,                        # (True or False)
        Comma                => 1,                        # (True or False)
        Space                => 0,                        # (True or False)
        Other                => 0,                        # (True or False)
        FieldInfo            => [ [1,1], [2,1], [3,1] ],  #Array Ref of array refs with num of Columns + Type of each column
        TrailingMinusNumbers => 1,                        # (True or False)
} );