Search code examples
excelpywin32

Excel: get cell value by defined name w/o knowing the sheet

I'm using pywin32 to read and write to an Excel workbook with multiple sheets. I would like to access cell values by their defined names. I could use

myWorkbook.Sheets(mySheet).Range("myCellName").Value

however, I don't always know what sheet the name is defined on. Right now, I work around that by using a range(sheet0, sheetx) to try every sheet. There is another function I thought I could use, that is a workbook method

myWorkbook.Names("myCellName").Value

but rather than returning the value that is stored in the cell "myCellName" it returns the sheet and cell reference of the cell, say 

u"='mySheet2'!$D$37"

Is there a more elegant way to directly access the value of a defined name - cell without knowing what sheet it is on?

Solution

  • You can get the value with

    MyWorkbook.Names("myCellName").RefersToRange

    And fwiw you can get the name sheet with

    MyWorkbook.Names("myCellName").RefersToRange.Parent.Name