#!perl
use strict;
use Win32::OLE 'in';
use Win32::OLE::Const 'Microsoft Excel';
$Win32::OLE::Warn = 3; # Die on Errors.

# config
my $dir      = 'c:/temp/';
my $specfile = 'names.xlsx'; 

my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
   || Win32::OLE->new('Excel.Application', 'Quit');  
$Excel->{DisplayAlerts} = 0;  
#$Excel->{Visible} = 1;  
   
my $Book  = $Excel->Workbooks->Open($dir.$specfile); 

# create named range
my %names = ( 
  'depth' =>['B1',559],
  'height'=>['B2',560],
  'width' =>['B3',590]
);

my $ws = $Book->Worksheets(1);
for my $nm (keys %names){
  my $rng = $names{$nm}->[0];
  # workbook scope
  $ws->Range($rng)->{'Name'} = $nm ; 
  $ws->Range($nm)->{'Value'} = $names{$nm}[1];
  # worksheet scope
  my $name = 'Sheet2!'.$nm;
  $ws->Range($rng)->{'Name'} = $name ; 
  $ws->Range($name)->{'Value'} = $names{$nm}[1];
}

# show all named ranges in workbook
print "\nWorkbook\n";
for my $nm (in $Book->Names){
  my $rng = $nm->{'RefersToRange'};
  printf "%s %s %s\n", $nm->{'Name'},$nm->{'Value'},$rng->{'Value'};
} 

# show all named ranges in worksheets
for my $i (1..2){
  print "\nWorksheet $i\n";
  my $ws = $Book->Worksheets($i);
  for my $nm (in $ws->Names){
    my $rng = $nm->{'RefersToRange'};
    printf "%s %s %s\n", $nm->{'Name'},$nm->{'Value'},$rng->{'Value'};
  }
}  

# close
$Book->Save;
$Excel->Quit();
undef $Book;
undef $Excel;
[download]