Try

#!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 $range = $Book->Worksheets(1)->Range("A1");
$range->{'Name'} = "Any_name"; # no spaces

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

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

I used your Perl on the spreadsheet with the variables defined.

Cell B1 was defined as depth and had the value of 559
Cell B2 was defined as height snd had the value of 560
Cell B3 was defined as Width and had the value of 590?

I got quite a lot of output but this well be because I simply removed cells to leave just a few to consider.
In this output I found the following
depth =CALC!$B$1
height =CALC!$B$2
width =CALC!$B$3

This seems usable since it effective says that the three variaibles depth, height and width are in the 3 cells B1, B2 and B3.
I think I can use the cell references to 'read' the values but I wondered what the most effect way of doing this.
Some clues will be appreciated

You can read the values using the name

for my $nm (in $Book->Names){
  my $rng = $ws->Range($nm);
  printf "%s %s\n", $nm->{'Name'},$rng->{'Value'};
}
[download]

poj

A quick reply, on which I might expand later. I haven't ever tried to find the specification for the box you describe, but what appears in there are "names". These date back at least to 123 v2/DOS and I think to v1a. In 123, they were named ranges, but Excel's concept of a name is far more powerful, although 99.lots% of people use them in the old 123 way because they don't understand the power. Hawley & Hawley, Excel Hacks, ISBN 9780596006259 (but I'm certain there's a later edition), dedicate an entire chapter to name hacks. I'm sure I've given other examples on this site, but I have demonstrated dynamic names in Re^3: Win32::Ole excel external data range, although you may need to read the thread to get the context.

Regards,

John Davies

Update: corrected 123 version

Hello merrymonk,

I am not really sure how this is happening on you excel sheet and you are not able to read the data by column and row. But there is a really nice simple tutorial How to read an Excel file in Perl that can you help you getting started.

To be honest without being able to replicate your problem I can not imagine any way that I could help you. Can you give it a try and based on the tutorial to write some code that replicates your problem?

The only alternative approach that I could think is to retrieve all data, e.g.

my @rows = Spreadsheet::Read::rows($book->[1]);
foreach my $i (1 .. scalar @rows) {
    foreach my $j (1 .. scalar @{$rows[$i-1]}) {
        say chr(64+$i) . " $j " . ($rows[$i-1][$j-1] // '');
    }
}
[download]

Through the retrieved data and having a reference to each row and column of the data in the sheet you can search for the variable and look up your references. Very briefly this is the only that I could think out of my mine.

Update: Adding sample of code for the tutorial provided above, including proposed solution using key(s) the row and column of the file and value(s) the variables of each cell. Sample of code bellow:

Update2: Based on Using structured references with Excel tables I created a sample of data and retreive data based on reference.

Hope this helps, BR.

Thanks for that I will try what you 'attached'.
I am trying to find out how the 'reference' has become a variable name. I have been reading spreadsheets for some time it was the use of 'variables' which was new to me.

Hello again merrymonk,

I have updated my reply to your question with a second sample of data. Is this something that you are looking for?

If not update the question with sample of data so we can experiment.

BR / Thanos

Seeking for Perl wisdom...on the process of learning...not there...yet!

I tried to use both the exmaples you sent. Sadly I do not have Excel::Writer with the version of Perl I use. Therefore I could not make much headway.

Hello Anonymous Monk,

I assume you are the same user with merrymonk. You could install the module Excel::Writer::XLSX.

In case you do not want to install the module (for any reason) you can use your own Excel sheet with the module Spreadsheet::Read to read the file and search for the value (reference) and it will return the column data.

Give it a try and let me know if it works for you.

BR / Thanos

Seeking for Perl wisdom...on the process of learning...not there...yet!