Why is detectImportOptions not sheet agnostic with a named Range?

I can use the following line

opts = detectImportOptions(workbookFile,'Sheet',sheetName,'Range',Range);

containing an excel range name (see name manager) in Range and the corresponding sheet name in sheetName. The thing is, excel already allows me to set range names for the whole workbook. So, no need to specify the sheet name. I'd like my script to be agnostic to changes in sheet names or positions.

How can I do that?

If I launch without sheetName

opts = detectImportOptions(workbookFile,'Range',Range);

Matlab doesn't find my range, although the range name in excel is given with "Scope: Workbook".

[Stephen23]

Good question. Contact TMW and make an enhancement request (under "Product Usage"):

https://www.mathworks.com/support/contact_us.html

[dpb]

What would/should it do when the definition isn't global, though, then?

[EnerKíte GmbH]

Same as now: if it's not defined in the default sheet (nor globally), then it doesn't find the range.

[EnerKíte GmbH]

Made an enhancement request, we'll see...

________________

don't throw error "Unable to determine range" in detectImportOptions, if Range name is unambiguous

If the Range Name is existent and unambiguous, Matlab could very well load the correct range without "knowing" the sheetname, but throws an error instead. See https://de.mathworks.com/matlabcentral/answers/2139036-why-is-detectimportoptions-not-sheet-agnostic-with-a-named-range?s_tid=srchtitle

________________

Answer 1

[dpb]

Because the whole suite for workbooks is designed around working with a single worksheet at a time; none of the functions that use an importoptions object support reading more than a single sheet at a tme.(*)

Conceivabley readmatrix or readcell could return a 3D array, but it's a functionality not envisioned by the developers.

(*)However, as a quality of implementation detail, I would have expected the second form to have read the range for the first sheet in the workbook, presuming it (the range) is defined for it since the default behavior is to read the first sheet. But I would have no expectation of any of the routines returning data from more than one sheet on a single call; they are not documented to do that; the sheet input parameter is always defined as a single sheet either by name or number or an implied 1 if not given.

ADDENDUM/ERRATUM:

I was thinking incorrectly as @Stephen23 pointed out regarding the multiple sheet data return, sorry.

Does the present implementation require the sheet name be explicit to a particular sheet to return the correct data? If the range definition is global, then the internal reference should be explicit as to which sheet actually contains the data range and it should return the correct value regardless which sheet is used to satisfy the input requirement. I've not poked at the API to see how it operates internally; I've written everything I've done such that it finds things by column headers rather than by naming ranges that's just another step in creating the Excel sheet...if it doesn't work with a universal 'Sheet',1 as the input for the sheet, then I'd amend the previous to the level of a bug instead of an enhancement.

The possible issue and complicating factor on the development side is the existence of a named range that is local in scope but on multiple sheets. I can see why Mathworks may have chosen to not deal with it for that reason and I kinda' expect the response to the enhancement request will be "nyet!".

[Stephen23]

"Conceivabley readmatrix or readcell could return a 3D array, but it's a functionality not envisioned by the developers."

A named range defined with workbook scope refers to exactly one worksheet.

[dpb]

Yeah, I see that now...I was thinking it would be similar functionality as to the sheet scope, my bad.

[EnerKíte GmbH]

As far as I see in Excel, the Range references contain the sheetname anyway, no matter the scope I chose. I think, the only difference is, whether the range's name must be unique sheet-wise or workbook-wise. Update: I see, a sheet-scoped name doesn't conflict with the same name given workbook-scoped. But workbook-scoped names conflict with each other and sheet-scoped names too.

So, Matlab could just look for the Range name workbook-wide, independet of the scope it is defined for, and chose it. And only throw error (i.e. "ask" for sheetname), if if it really finds two ranges by the same name. Or prefer one of them, i.e. first the global one, then the first of the locals or something like that.

Answer 2

[dpb]

Potential workaround/addition---

I created a rudimentary ActiveX component to query a workbook for the contained named ranges; it isn't complete and I've not tested just how the difference between global and worksheet names is differentiated; I've not found a property that indicates which and I've only tested and the attached code only references the first sheet in the workbook I tested it on; I didn't have any others with already builtin names at had for testing.

This returns a cell array of the names, addresses and name scope found; your mission if you choose to accept is to adapt to your situation and augment the range in your application to allow the generic import you're looking for.

function [name,range,scope]=GetAllNames(excel)
% return all workbook defined names
  
  wbk=excel.ActiveWorkbook;
  nms=wbk.Names;
  nNames=nms.Count;
  wks=wbk.Worksheets(1);
  Cells=cell(nNames,3);
  for r=1:nNames
      Cells(r,1)= {nms.Item(r).Name};
    try
      Cells(r,2)={nms.Item(r).RefersToRange.Address};
      Cells(r,3)={nms.Item(r).Parent.Name};
      if matches(wbk.Name,Cells(r,3)), Cells(r,3)={'Global'}; end
    catch
      err=lasterror;
      if contains(err.message,'0x800A03EC')
        Cells(r, 2)= {'#REF!'};
        Cells(r,3)={nms.Item(r).Parent.Name};
        if matches(wbk.Name,Cells(r,3)), Cells(r,3)={'Global'}; end
        continue
      else
        excel.ActiveWorkbook.Close
        excel.Quit
        warning('Excel GetAllNames Failure. Returning')
          name=Cells(1:r,1); range=Cells(1:r,2); scope=Cells(1:r,3);
        return
      end
    end
  end
  name=Cells(:,1);
  range=Cells(:,2);
  scope=Cells(:,3);
end

The only way I found to be able to determine scope was by querying the .Parent property; this returns the file name for global variables; I then turned that into the string "Global" instead of returning the file name; the sheet-specific return the sheet name.

I incorporated this in a enhanced Excel_utils class that works by first opening the ActiveX session and passing in the handle to the COM process. I wrote this to return the names from the active workbook already open, it can be easily modified to open the file passed to it instead.

To use this, the preamble needs would be

    File='FullyQualifiedFileName.xlsx';                 % must be fully qualified, no shortcuts will do
    excel=actxserver('Excel.Application')               % create the COM process
    excel.Workbooks.Open(File)                          % open the desired file
    [names,ranges]=GetAllNames(excel);                  % do the magic
    excel.ActiveWorkbook.Close;                         % close the file
    excel.Quit                                          % stop the connection
    clear excel                                         % don't leave hanging around in workspace

The error handling inside the function will catch a name with a bad reference and return "#REF!" for the address; any other error will go ahead and close the file and shut down COM connection so if it gives you the warning message then the last steps won't be needed (hopefully). If you play further, you'll undoubtedly crash and burn more than once so be prepared to occasionally have to kill Excel with task manager. Using breakpoints in debugger will help immeasureably in that regards, you can play at the command line in the debugger and it will trap the errors while you're testing new features; if you just let it run then it'll crash before you can do anything and leave the file open, etc., ...

A sample output at the debugger level after I did ad a couple of sheet scope variables to the test workbook looks like:

K>> Cells
Cells =
  36×3 cell array
    {'ActivityFee'                     }    {'$H$5'       }    {'Global'   }
    ...
    {'Budget_Year'                     }    {'$U$1'       }    {'Global'   }
    {'Sheet1!CellA1'                   }    {'$A$1'       }    {'Sheet1'   }
    {'Cheer'                           }    {'$U$11'      }    {'Global'   }
    {'County'                          }    {'$C$5'       }    {'Global'   }
    {'OutState'                        }    {'$C$8'       }    {'Global'   }
    {'Complete!Print_Area'             }    {'$A$3:$N$130'}    {'Complete' }
    {'RateBorder'                      }    {'$B$7'       }    {'Global'   }
    ...
    {'RateInState'                     }    {'$B$6'       }    {'Global'   }
    {'RateOnline'                      }    {'$B$9'       }    {'Global'   }
    {'Soccer_Programs'                 }    {'$U$9'       }    {'Global'   }
    ...
    {'Athletics!TotalInStateAthletics' }    {'$H$21'      }    {'Athletics'}
    {'Athletics!TotalOutStateAthletics'}    {'$H$10'      }    {'Athletics'}
K>>

The named sheets show the names for the sheets while the added new sheet reflects the default 'Sheet1' string.

The link to <MS VBA Name Object> documentation will let you get started digging further...

Good luck...

[dpb]

ADDENDUM

"... just how the difference between global and worksheet names is differentiated; I've not found a property that indicates which "

OK, closer observation shows that the local names are prefixed with "Sheetname!" in the address so simply checking for the "!" in the address or matching a sheet name works as well without querying the parent value.